Database

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

22-Jun-05 6:03

BinaryHead wrote:
make sure no two persons can UPDATE some of the selected transactions at the same time.

transactions? You mean rows, surely.

Personally I'd create a stored procedure for this sort of thing (if possible) which has the added advantage of a single round trip to the database.

Anyway, you can create a transaction and run multiple commands in the same transaction and then commit it. There is an example here on MSDN[^]

Re: ADO.NET - Best way to use the mighty SELECT

david cohoon22-Jun-05 8:06

david cohoon

22-Jun-05 8:06

There are many different ways of ensuring that the rows are not modified while you are accessing them. But, let me ask you first - is it feasable that the data in the db can be altered by means other than your application?

Ensuring optimitic locking inside your application only works if everyone is using your application to access the data. If others might have access to the data outside of your application, you'll most likely want to push all your updates into a transaction and set the isolation level accordingingly.

Re: ADO.NET - Best way to use the mighty SELECT

BinaryHead22-Jun-05 22:25

BinaryHead

22-Jun-05 22:25

There could be many people accessing the same data at the same time using this application (many instances/copies of my program) Im trying to build (but not accessing the data with some third party application).

Im just scared of one thing:
- If two different programs, A and B, are running at the same time(both using the same database) and A requests some rows with the DataReader (f.ex. SELECT... where some Boolean == false) and at the same time B updates some of the SAME rows (UPDATE...where ..SET Boolean == true).

Is the DataREader in A smart enough to SELECT some rows, and then while looping through them, figure out that one of the rows has been changed (Boolean == true) so it should skip it in the loop? (or are the rows that the DataReader returns just copied into memory and then looped blindly like the DataSet with Adapter.Fill() / Adapter.Update()? ).

replication: remote login error

NickKnife22-Jun-05 2:02

NickKnife

22-Jun-05 2:02

Hey there
Im busy trying to set up transactional replication between a server in one city and a backup server in another. When i try Configure the publisher/distributor I get to the end of the wizard and while trying to configure the distributor it gives me an error:
Error 18483: couldnt connect to server x because 'distributor_admin' is not defined as a remote login at the server
Im pretty new to this whole replication thing
Any advice?
I would really appreciate it.
Nick

SQL Server 2000 CE Edition...

Jan van den Baard22-Jun-05 1:54

Jan van den Baard

22-Jun-05 1:54

I have a simple CE.NET application running on a embedded machine running Windows CE.NET 4.2 and SQL Server 2000 CE Edition 2.0. In this application I am using a standard SqlCeCommand object to run the following query:

<br />
UPDATE tblschedules<br />
SET tblschedules.result =<br />
(<br />
    CASE WHEN EXISTS<br />
    (<br />
        SELECT * FROM tblscheduledetails<br />
        WHERE tblscheduledetails.schedule = tblschedules.id AND<br />
	      tblscheduledetails.result = 1<br />
    )<br />
    THEN 1<br />
    ELSE 0<br />
    END<br />
)

The query results in a "A native exception occured" just as soon as I do a ExecuteNonQuery() on the object. The query itself runs without problems when running it from the CE Query Analizer. Does anybody have an idea what might be the problem?

We are the all singing, all dancing crap of the world. - Tyler Durden

SQL SERVER 2005 INSTED OF Trigger

Yaakov Davis22-Jun-05 1:28

Yaakov Davis

22-Jun-05 1:28

Hi.
I set an INSTED OF INSERT trigger on a table, that deny the addition of rows in some conditions.

My question is:
When I run the insert command, I get the (X row(s) affected) message, even if that isn't the real number of added rows.

I tried to set the @@ROWCOUNT function, but it turns out this isn't the way.
Is there any way to change the affected rows count of a trigger / sp?

Thanks,
Yaakov

SQL Server Manager 2005 animated wait thingie

nxtwothou21-Jun-05 10:37

nxtwothou

21-Jun-05 10:37

I went to a TechNet presentation about SQL Server 2005 and for some reason fell in love with the circular "busy" animation it plays(which, when completed, changes to a big checkmark in a circle). I was wondering if anyone knew if these where public images and are going to be included in the VS.Net 2005(I know they are including a file of icons and animations that are used in VS.Net, but not sure if from any of their other products).

Or if anyone knew a PD file out there that looks simular.

Connection Pooling

anju21-Jun-05 8:30

21-Jun-05 8:30

Hi All,

I would like to know;How Connection pooling will impact on security?

Thanks in Advance. Rose | [Rose]

anju

Colin Angus Mackay21-Jun-05 8:38

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

21-Jun-05 8:38

anju wrote:
How Connection pooling will impact on security?

To my knowledge it doesn't.

Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created.

anju21-Jun-05 8:44

21-Jun-05 8:44

Thank you for your quick reply.
What I am looking is ....

To implement connection pooling the connection strings must be identical.(am I right?).If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?.

anju

Colin Angus Mackay21-Jun-05 8:57

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

21-Jun-05 8:57

anju wrote:
To implement connection pooling the connection strings must be identical.(am I right?).

Yes. That is correct.

anju wrote:
If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?.

Because you create a connection, use the connection and then close the connection. Next time you access the database you repeat the create, use, close routine again. The second time round the connection will be in the pool and you get it quickly.

anju21-Jun-05 9:11

21-Jun-05 9:11

If we want performance and scalability,Do we need to compramise about security?

anju

Colin Angus Mackay21-Jun-05 9:18

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

21-Jun-05 9:18

anju wrote:
Do we need to compramise about security?

No.

anju21-Jun-05 9:23

21-Jun-05 9:23

Then, Could you pleae explain me the way to Manage Security with Connection Pooling.

anju

Colin Angus Mackay21-Jun-05 10:04

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

21-Jun-05 10:04

As I have said already: Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created.

It is NOT a way to manage security. It is a way to repeatedly use the same connection to the database.

anju21-Jun-05 10:14

21-Jun-05 10:14

Thank you for your explanation.

anju

Luis Alonso Ramos21-Jun-05 16:58

Luis Alonso Ramos

21-Jun-05 16:58

anju wrote:
for different UserIds and Passwords the connection string is always different

Yes it is, but, then you don't use different user IDs/passwords in your application. If your application needs logins, you create a table in your database with the user name and passwords *for your application*.

Then you create one single SQL Server user that only has access to run stored procedures on your application's database. This is the user ID you use everywhere in your application to access your database (of course, you must have stored procedures to access all tables.)

When a user logs in your application, you find out in your database using the stored procedures what permissions that user has, and selectively enable/disable parts of your application.

But you only should have one database user ID/password, and use that in your connection string (never sa). This way, all the connection strings will be the same, and connection pooling will work fine.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Colin Angus Mackay21-Jun-05 20:25

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

21-Jun-05 20:25

Luis Alonso Ramos wrote:
But you only should have one database user ID/password, and use that in your connection string (never sa).

Actually, I'd use windows authentication so that the user and password aren't stored anywhere and don't have the possibility of being discovered.

Mike Dimmick22-Jun-05 2:43

Mike Dimmick

22-Jun-05 2:43

I'd agree with that in general, but there are times that it's not practical, for example if you have a service that's running as SYSTEM with the database on another machine. Yes, I know that it's bad practice to have a service running as SYSTEM unless absolutely necessary - unfortunately this is a legacy service with UI and therefore has to run as an interactive service, hence it has to run as SYSTEM.

It's useful to note that you don't need a domain to set this up, but if you don't have one, you'll need to create user accounts for the appropriate users on the server hosting SQL Server, and add them to SQL Server's logins. You then need to synchronize the passwords for those user accounts between the machine that the client runs on and the server hosting SQL.

One thing you can do with Windows Authentication that you can't with SQL Server Authentication is to set up groups of users with particular permissions.

Stability. What an interesting concept. -- Chris Maunder

Colin Angus Mackay22-Jun-05 2:58

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

22-Jun-05 2:58

Mike Dimmick wrote:
I'd agree with that in general, but there are times that it's not practical

Or if the SQL Server is being accessed from a Unix machine. Just to give another example.

Maybe I should have said "where ever practical, use Windows Authentication". Big Grin | :-D