Best program for SQL database manipulation

Hello All

I am a relative beginner to SQL databases & new to this forum, so please bear with me if my query is too basic and advise if this question belongs somewhere else

I began working at a company that uses a program that stores data in an SQL database running off a Firebird engine

The program itself doesnt come with database management/administration module, so I'll need to use an external program for manipulating data in the tables that the database contains.

I have relatively little knowledge in SQL programming, which is why I would like to know which is the most powerful program for SQL database updating / manipulation?

This database has tables that has an infinite number of joins with other tables - Even MS Access wasnt able to open a few tables in this database because of the number of joins. I have tried Access & Lotus Approach, Approach manages to do a better job than Access, it open the tables & seems like it will let me import external data directly into the SQL table, but takes forever & usually just bums out giving an error after a very long wait..

My question is - Apart from Access & Approach, are there any more powerful, yet user friendly programs out there that can help me update data directly into SQL tables? What options do I have - the tasks I need to perform are pretty simple updating & cleaning of data already in there

Please, I will hugely appreciate any pointers that you guys the experts might have for me in this regard

Thanks
AlexTry Microsoft SQL Server 2005 Express. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/sql

Best Practice for MSDE User permissions

Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.

> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve

Best Practice & Other

Hi All
I have the following replication setup:
Replication Type: Transactional
Database Size: Circa 35gb
Articles: All articles are published and are required to be at the
subscriber.
Server1: Publisher, SQL 2000 Sp3 (W2k3 sp1)
Server2: Distributor, SQL 2000 Sp4 (W2k3 R2 sp1)
Server3: Subscriber SQL 2000 Sp3 (W2k3 sp1)
Server 3 has the pull subscription.
Other Info: Server 2 did have SQL2005 installed. It's since been
uninstalled and resolved some transactional issues.
This is the environment that I have inherited.
Based on reading around, this appears to be an acceptable best
practice method. I might press against throwing SP4 on server 1 and
server 3.
Are there any amazing troubleshooting tips for this process around?
there are times when the transactional replication does not work. I
think it times out so I'm looking at changing the time out to 3000
seconds.
One issue I've found is that there was a duplicated transaction that
managed to get through. This crashed the envrionment and there was no
choice but to create a new snapshot - it takes hours. How could we
best avoid this? If we deleted these transactions from the subscriber
(difficult due to referential integrity) how would the system know to
replicate them again?
Another one that we've had is where the old transactions are held in
the log. I need to flush them out so that the log can be truncated and
then reduced in size. Any further hints and tips?
I'm simply after some good practice methods to help troubleshoot and
plan the replication process since we're investing time and resource
in it rather heavily. We'll move to 2005 once things are stable.
Thanks in advance for the help.
Simon
Hi Paul
Thanks for the advice. Can you think of any really good
troubleshooting methods when things are gone wrong. I don't mind how
generic they are, it's just good to excercise the brain on new ways of
handling problems.
Cheers
Simon