Database

4-Jan-10 7:05

Hi guys...I do not know whether it is right platform to ask the question. Suppose I update databse on my laptop. Now I want the same database on another location, to be updated automatically...obviously wirelessly. How to do that?? Confused | :confused:

modified on Monday, January 4, 2010 1:35 PM

Stryder_14-Jan-10 9:47

Stryder_1

4-Jan-10 9:47

Hi,

It will likely depend on the database you are using. Also, is this connected at the same time, or do you mean to have the database on your laptop updated, then when you connect to the other location, it updates at that time?

Two main ways:
1) Replication - Review this in your db documentation and return if you have further questions.
2) Do it yourself in code - code your app to send the changes when you connect the the remote database.

Thanks.

AmbiguousName5-Jan-10 3:47

5-Jan-10 3:47

There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device. Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you. Having said this I"ll use your first suggestion.....Replication

Mycroft Holmes4-Jan-10 13:17

Mycroft Holmes

4-Jan-10 13:17

What happens to the updates if your wifi connection is down?

What happens when there are 2 or more local databases?

This is a really bad design. If you are working offline (local database) then you need to implement a replication strategy. This involves more than just shoving data back and forth.

Never underestimate the power of human stupidity
RAH

AmbiguousName5-Jan-10 3:54

5-Jan-10 3:54

Ans#1: what happens if your nearest ATM is not working well. You will have to wait untill it is fixed.
Ans#2: In case of 2 or more local DBs, nearest one will be selected to be updated.

Apart from this...im a student so any stupidity is hopefully forgiven Smile | :)

Mycroft Holmes5-Jan-10 11:05

Mycroft Holmes

5-Jan-10 11:05

Muzammil Saeed wrote:
Apart from this...im a student so any stupidity is hopefully forgiven

Absolutely

This is not necessarily stupid, just part of the learning process, what sort of system are you trying to design you have completely confused me with ATM, wireless and nearest database.

Never underestimate the power of human stupidity
RAH

AmbiguousName5-Jan-10 13:47

exported the first column of an excel file to a table

5-Jan-10 13:47

There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device.Hence Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you

deva23-Jan-10 23:05

deva2

3-Jan-10 23:05

Hello
I work with sqlserver2005 express ,I try to populate my table student by:
the first field data by the first column of an excel file.
other field by 123.12 ...
My code is

INSERT INTO student (Ch1, ch2, ch3)<br />
SELECT ch1, 123 AS ch2, 124 AS ch3<br />
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',<br />
'Excel 8.0;Database=C:\test\xltest.xls', [Feuil1$])

but i have this mesage :
Unable to initialize the object from the data source OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Thanks Smile | :)

loop problem

Jassim Rahma3-Jan-10 21:02

3-Jan-10 21:02

I am using the following ocde to loop through existign records and create account summart record for every client but it seems looping with no end and it's creating an endless account summary records for the first client only.. what's wrong?

USE takhlees

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DELETE FROM accounts_summary

-- reset primary key

INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')

DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Jassim Rahma3-Jan-10 22:30

3-Jan-10 22:30

does it have anything to do with this:

WHILE @@FETCH_STATUS = 0

Jassim Rahma3-Jan-10 22:39

3-Jan-10 22:39

this is the modeified code ro make the SCOPE_IDENTITY clear but of course still the same problem

DECLARE @rec_id int, @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, 0, @client_name)
	SET @rec_id = SCOPE_IDENTITY()
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), @rec_id), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = @rec_id
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Mycroft Holmes3-Jan-10 23:45

Mycroft Holmes

3-Jan-10 23:45

Dammed if I can see any problem with your cursor. Try breaking it down to just print the @client_id for each loop to make sure it is performing correctly.

I have an issue with you using the @account_number as a FK between client and account. You should be using the @rec_id as the FK on the client table. Keys should be stupid, you know no intelligence and you are using a formatted string as the key instead of the identity value - bad design!

Never underestimate the power of human stupidity
RAH

Jassim Rahma4-Jan-10 2:01

4-Jan-10 2:01

i did PRINT and it's just printing one record# 25

Corporal Agarn4-Jan-10 3:56

Corporal Agarn

4-Jan-10 3:56

Try just this

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Client_ID

	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Also make sure your select statement is returning what you think.

Tim Carmichael4-Jan-10 5:54

Tim Carmichael

4-Jan-10 5:54

I've had problems with cursors not working correctly, or, telling me the cursor is already open.
Can you change from using a cursor to using a temporary table or table variable?
Insert the select data with an identity field into the temporary table/table variable and
then create a loop to process each record.

Hope it helps.

Tim

Debugging in SQL Server 2005

Ben Fair6-Jan-10 7:41

Ben Fair

6-Jan-10 7:41

Could it possibly have anything to do with using GETDATE() to construct 'random' portions of the string? Each pass through the loop will render the same values until the server system clock flips to the next day. It seems to me you will always get the exact same value for each pass of the loop until the next day arrives. I also don't understand your use of the RIGHT() function, perhaps it is some odd way of padding with 0's. In each case it seems you concatenate 000's + some value and then pull the 'some value' you just concatenated from the 000's; why not just use the values themselves and not clutter it up by concatenating the 000's and values together only to turn around and take the values off? If you need to pad try using the REPLICATE() function, as it will be easier to read:

DECLARE @hold varchar(50)
SET @hold = convert(varchar(7), SCOPE_IDENTITY()) -- or @recid
SET @hold = REPLICATE('0', 7 - LEN(@hold)) + @hold -- pad to desired length of 7
-- REPLICATE('0', 0) does not produce an error

Also if you truly need to get a random value try using the NEWID() function which will return a random GUID.

Hold on a second here... Don't you think you might be putting the horse ahead of the cart?

meeram3953-Jan-10 18:45

meeram395

3-Jan-10 18:45

I tried to debug a stored procedue in VS 2005 as mention in this ^link.

Howver, when I tried to select, 'Step into Stored Procedure', I am getting an message in the output window saying that ' Cancelled by User'. Not sure what is happening. Any idea?

I even tried to give admin rights by executing the sp_addsrvrolemember sp in master db. This returns the message, 'Command(s) completed successfully'. Hope it has given the admin rights.

But still I am getting the same message as above, not able to debug the stored procedure. Any idea what is happening? I am using VS 2008.

Thanks
meeram395

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Re: Debugging in SQL Server 2005

meeram3953-Jan-10 19:09

meeram395

3-Jan-10 19:09

I got the fix. I don't have the rdbgsetup.exe in 1033 folder, even though 1033 folder is present. Do I need to reinstall this or is it available for free download from net? I tried in google, but didn't get anything such.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Multiple values associated with a single value

shrims4u2-Jan-10 22:19

shrims4u

2-Jan-10 22:19

Hi all,

I want a trick as to how to add multiple values for a single value .

For example :

A Person named "A" has two cars "Car1" , "Car2"

How do I design the database for this ??

Re: Multiple values associated with a single value

Rob Philpott2-Jan-10 22:32

Rob Philpott

2-Jan-10 22:32

You have a table Person keyed say on PersonId.

You also have a table Car, keyed on CarId. Also in this table you have a PersonId fogeign key to the first table.

Each Person can then own multiple cars, and to see which you need to do a query with a 'join'.

Regards,
Rob Philpott.

Re: Multiple values associated with a single value

Corporal Agarn4-Jan-10 2:18

Corporal Agarn

4-Jan-10 2:18

Hello,
Rob Philpott has the right idea. Do not forget to index the combination table.

If you want to have only one table then I would suggest having an ID, Person, and Car columns that way the ID could be your primary key and you could have an index on the person column and one on the car column.

However, Rob's way is the proper way to setup a database.

Re: Multiple values associated with a single value

AmbiguousName4-Jan-10 7:32