Database

12-Jun-12 11:36

I have an application where the client is working against a local DB, and each night will upload its data to the server, so I need Id's that are unique in the server's DB. The server and client DB's are identical.

Here's the tables:

CREATE TABLE tblOperators
	(OperatorId		INT	IDENTITY		NOT NULL,
	 AccountNo		VARCHAR(10)			NULL,
	 [Name]			VARCHAR(100)		NOT NULL,
	 Address1		VARCHAR(100)		NOT NULL,
	 Address2		VARCHAR(100)		NULL,
	 City			VARCHAR(100)		NOT NULL,
	 [State]		VARCHAR(2)			NOT NULL,
	 ZipCode		VARCHAR(10)			NOT NULL,
	 Phone			VARCHAR(10)			NOT NULL,
	 Email			VARCHAR(100)		NULL,
	 IsActive		BIT					NULL)


CREATE TABLE tblFacilities
	(FacilityId			INT	IDENTITY		NOT NULL,
	 OperatorId			INT					NOT NULL,
	 FormOfPaymentId	INT					NULL,
	 SaleTypeId			INT					NULL,
	 FacilityName		VARCHAR(100)		NULL,
	 FacilityAddress1	VARCHAR(100)		NULL,
	 FacilityAddress2	VARCHAR(100)		NULL,
	 FacilityCity		VARCHAR(100)		NULL,
	 FacilityState		VARCHAR(02)			NULL,
	 FacilityZip		VARCHAR(10)			NULL,
	 FacilityPhone		VARCHAR(10)			NULL,
	 BankName			VARCHAR(100)		NULL,
	 ManagerName		VARCHAR(100)		NULL,
	 ManagerPhone		VARCHAR(10)			NULL,
	 ManagerEmail		VARCHAR(100)		NULL,
	 PickupDays			VARCHAR(100)		NULL,
	 IsActive			BIT					NULL)

You can see the Operator and Facility Id's. They won't work once sent to the server.

I'm not sure how to define compound keys. Can someone point me in the right direction?

Thanks

If it's not broken, fix it until it is

PIEBALDconsult12-Jun-12 16:45

PIEBALDconsult

12-Jun-12 16:45

That is a big reason not to use integers as keys.

You may need to rethink your whole application.
I mainly wonder why the clients would be adding records to these tables.

However, in cases like that each client should probably have its own ID and therefore you could make the key be both the ClientID and the other Id.

Kevin Marois12-Jun-12 18:13

12-Jun-12 18:13

PIEBALDconsult wrote:
You may need to rethink your whole application.
I mainly wonder why the
clients would be adding records to these tables.

What if the client doesn't have a network connection? Where would the store the data. This application cannot have down time in the event of a lost connection. So the app will have a local data store and update the server periodically.

So, since new records can be added in the client's DB, I need to make sure the PK's are unique.

Thanks

If it's not broken, fix it until it is

PIEBALDconsult13-Jun-12 4:10

PIEBALDconsult

13-Jun-12 4:10

Yes, but why would the client be adding records to those tables?

Kevin Marois wrote:
PK's are unique

Globally unique?

Kevin Marois13-Jun-12 6:33

13-Jun-12 6:33

I don't understand why you're questioning this.

The software is installed locally on a user's PC, along with a local copy of the DB. The software does CRUD operations on the local copy of the DB.

Then, at night, or at least once per week, the software uploads all new/changed/deleted records to the server.

If it's not broken, fix it until it is

Eddy Vluggen13-Jun-12 1:21

13-Jun-12 1:21

PIEBALDconsult wrote:
That is a big reason not to use integers as keys.

Yeah, people being too lazy. The problem goes away with GUIDs, since that allows for laziness.

Bastard Programmer from Hell Suspicious | :suss:

Jörgen Andersson12-Jun-12 21:56

Jörgen Andersson

12-Jun-12 21:56

Kevin Marois wrote:
and each night will upload its data to the server

Is this a oneway communication where the data is only uploaded, or will it synchronise the data with the server?
If it's just an upload you don't need to have global keys, you can add new keys at the server when uploading.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Kevin Marois13-Jun-12 6:34

13-Jun-12 6:34

There will be two-way communicaation

If it's not broken, fix it until it is

Eddy Vluggen13-Jun-12 1:19

Maybe look at Set IDENTITY_INSERT On/Off

13-Jun-12 1:19

I don't see a key, only an identity-field. If that identity is the key, and you need a quick solution, add a second column with a GUID and promote that to be the primary key.

I'd recommend to have a "real" key; take those columns that uniquely identify a record, and use those as a key, and the identity merely as an alternative shortcut to that.

Bastard Programmer from Hell Suspicious | :suss:

David Mujica13-Jun-12 2:56

David Mujica

13-Jun-12 2:56

You may have to change the way you are bringing data from the client DB to your server DB.

Check this article out ...

http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server[^]

Remember to vote if this helps. Thumbs Up | :thumbsup:

Accessing Oracle database

Dangermouse9912-Jun-12 3:37

Dangermouse99

12-Jun-12 3:37

Hi we have an Oracle 10g database that I would like to connect to, the database has been written by a 3rd party.

I was wondering what free tools I can use to access the tables, etc. Something similar to SQL Server management studio would be nice.

Also if I need to install something else Oracle related, dll or whatever, in order to connect to it. We have the server, database login, etc, just need to connect to it!
Thanks

Pete O'Hanlon12-Jun-12 3:49

Pete O'Hanlon

12-Jun-12 3:49

Dangermouse99 wrote:
Something similar to SQL Server management studio would be nice.

Something like Sql Developer[^] perhaps?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Dangermouse9912-Jun-12 4:08

Dangermouse99

12-Jun-12 4:08

Thanks, that looks good.

Pete O'Hanlon12-Jun-12 5:34

Pete O'Hanlon

12-Jun-12 5:34

No problem. Glad to help.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

PIEBALDconsult12-Jun-12 7:49

PIEBALDconsult

12-Jun-12 7:49

I downloaded Oracle's ADO.net connector and added support for it in my database classes. That meant that I could access Oracle databases from my console and WinForms apps, including one that allows the user to enter ad hoc queries and view the schema.

SQL Server (image) data type

Jörgen Andersson12-Jun-12 21:33

Jörgen Andersson

12-Jun-12 21:33

SQLTools[^] if you want it lightweight.
TOra[^] is at the other end of the spectrum.
Both are opensource.

SQL Developer and Oracle Developer Tools for Visual Studio has already been mentioned. They are from Oracle and are supported, ... sort of.

You will also need to install some version of the Oracle Client which is available in several versions such as the normal Client, Instant Client or Oracle Data Provider for Dotnet.
Oracle Developer Tools for Visual Studio has the Client built in.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Midnight Ahri10-Jun-12 20:33

Midnight Ahri

10-Jun-12 20:33

I save image to database using byte array to SQL server image type, if i save 10 MB image, is it going to be bigger or smaller in database? D'Oh! | :doh:

Re: SQL Server (image) data type

Eddy Vluggen11-Jun-12 0:07

11-Jun-12 0:07

10 Mb will remain just that, unless you use a CompressionStream.

Bastard Programmer from Hell Suspicious | :suss:

MySQL Password

Tom M3W10-Jun-12 4:09

Tom M3W

10-Jun-12 4:09

I had a programmer in a foreign country develop an application for me, I had to dismiss him because he would not finish the program on time. I asked him for the source code to MySQL db and he would not provide it. I paid him so that wasn't the issue. I need to move my application from one cloud server to another company and I need help in figuring out how to break the db code to move the application. There are a lot of connection points in and out of the db so I can't just copy the db and move it over.

Thanks

Re: MySQL Password

Eddy Vluggen10-Jun-12 4:13