|
I have a database which is used to pass information about certain entities between two programs. Each entity will have a record for each program, containing, in simplified form:
The ID of the entity
The identity (0 or 1) of the program that created the record
Poll Time (used mainly to confirm that both programs are checking the database in timely fashion--not used by the synchronization operations themselves)
Data for the other program
A timestamp/rowversion for that data
A timestamp/rowversion for the other program's data (copied from the other program's record when it's observed)
The first two fields would form a primary key. Each program would check its own timestamp/rowversion against the other programs' to see whether any updates were required.
Two related questions:
-1- What should I use for the timestamp/rowversion? Possibilities I can see:
-a- Using a date/time record for the timestamp would probably work, but it could break if a record gets updated twice quickly. The risk is probably more theoretical than actual, but I'd prefer to avoid something that is structurally not sound.
-b- A 'timestamp' field would seem almost ideal, except that I would want to be able to update the 'other program' timestamp field without bumping my own.
-c- A GUID would work, but GUIDs are bulky and ugly.
-d- In an earlier SQL Server I could have used a bigint and had the update store @@DBTS (a 64-bit integer that was bumped every time a database access occurred). Unfortunately, in newer versions @@DBTS doesn't get bumped unless a row containing a timestamp/rowversion field is updated.
-e- I could have each row contain an ignored timestamp/rowversion field and store @@DBTS in a field I'm actually interested in. That would work, but having the unused field just to force a @@DBTS update seems goofy.
-f- I could use another single-column table as a 'ticket dispenser'. Each time I want a number, do an insert, select SCOPE_IDENTITY(), and empty out the table. That would probably work, but again seems goofy.
What's the right approach?
-2- Is there any good way to find pairs of records, such that the entity ID's are equal, the record types are 0 and 1, and the "My Timestamp" field of the first does not math the "Other Timestamp" field of the second or vice versa, or is the data format described above really not a good design?
|
|
|
|
|
Hi.
In a system that has SQL Server 2005, how can I know the instance names of the SQL Server?
Best wishes
|
|
|
|
|
If there is only 1 server installed, it has no instances - but only a default instance which does not need to be called with a name.
If there is more, then I think you need to talk to the guys who've set up multiple SQL Server instances.
|
|
|
|
|
goto ths path
control pannel->administrative tools->computer management->
now you have to expand the services and application and in subtree expand the microsoft sql server
just it.
the instance name of your sql server is there.
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
Hey.
I understand that in sql server 2005 you can write statements to update several tables at once using join. Is that correct? I'm trying to this but keep getting an "Incorrect syntax near 'INNER'" error.
My SQL is
<br />
UPDATE Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID SET Courses.IsDeleted=1, Deliveries.IsDeleted=1, Courses.DeletedOn=@DeletedOn, Deliveries.DeletedOn=@DeletedOn WHERE Courses.CourseID=@CourseID<br />
Any help would be appreciated
|
|
|
|
|
I've never seen this done, as far as I know you can only update one table in an update statment. Your error message suggests that it won't work either.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok. It would be nice if you could do it though. how about DELETE JOIN? I'm sure you can do that
forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2541925&SiteID=1 (its a little slow today)
But for some reason my sql isnt working for that either. ive tried numerous combinations with this being the most sensible i think:
<br />
DELETE FROM Courses INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID WHERE Courses.CourseID = @CourseID
exactly the same error : "Incorrect syntax near 'INNER'"
|
|
|
|
|
You can - as far as I know - only update one base table at the time.
You can however use INNER JOINS for updateable queries - but still only when they affect one table such .
The problem in your syntax is not that you try to update two tables at once, but that you have the wrong syntax for updating using a join.
The easy way would be something like:
UPDATE Courses
SET Courses.IsDeleted=1, Courses.DeletedOn=@DeletedOn
FROM Cources
INNER JOIN Deliveries ON Courses.CourseID = Deliveries.CourseID
WHERE Courses.CourseID=@CourseID
And then run it a second time for updates in the Deliveries table.
If you even need to use a JOIN for your update when you can't do both tables at once, of course.
|
|
|
|
|
Thanks for your advice. At the moment my code is pretty inefficient so i can see how the update join may speed things up and make my life a little easier!
|
|
|
|
|
hi every one
i have problem when i update my database my primry key is three column how can i write my sql statment
i am programing using access db and vb.net
when i write
sql="update [Section] set Cap=4 where Sectionid=123 and techid=123"
it give error and when i but sectionid only it also give error
|
|
|
|
|
It would help if you said what error, but I'll hazard a guess that you are trying to create a duplicate key. Check that you don't already have a record where Cap=4 and Sectionid=123 and techid=123
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes, their will be duplicate because i can not but "and techid=123" in the sql it give error that it can not know what is after Sectionid=123 so i am asking about how can i write this
thanks
|
|
|
|
|
i think the real challenge is trying to figure out what your question is.
|
|
|
|
|
hi
this is the error
Syntax error (missing operator) in query expression 'SectionID = 847 AND'.
and sectionid is number
|
|
|
|
|
I suspect you have some charater datatypes, you need to encluse the values in quotes
sectionid = '123' etc
Bob
Ashfield Consultants Ltd
|
|
|
|
|
but sectionid of type number not text
|
|
|
|
|
Post your table create script and then I'll see what I can do
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks, but i found the solution
|
|
|
|
|
asha_s wrote: thanks, but i found the solution
which was?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
How can I restore the .bak file to Server.
I want to move database between client and server. I use SQL Server Management Studio Express, my databese in my local computer and I get backup (its .bak file). And I want to Restore database on the server. I'm left-click to database on the server after that I select; Tasks --> Restore --> Database.. so open dialog box, and I select "From Device" but I can't add .bak file which is on my computer. How can I export to database ?
Best Regard...
|
|
|
|
|
You have to copy the bak file to the server. Then you can restore the backup to the server.
|
|
|
|
|
hai all
i worked in sql server 2000.i have sqldatabase named as acs.
i want to install this database in other computer.so how to create this sql database setup to install in other computer.
thanks in advance.
|
|
|
|
|
Get Backup, and Restore to new database server. You can use "SQL Server Management Studio Express" for this operation.
Step 1: Attach Database
Open "SQL Server Management Studio Express"
right-click to database folder
select Attach...
Step 2: Backup database.
Select attached database where in the database folder
right-click to database --> Tasks --> Back Up..
Now you have .bak file
Step 3: Restore database
Open "SQL Server Management Studio Express"
Select new database (its empty)
right-click to database --> Tasks --> Restore --> Database...
select "From Device" radio button and browse .bak file
Best Regards...
|
|
|
|
|
Hi,
In this similar process as mentioned in step 1, I have attached the database successfully (which I have detached previously), but it is attached as "READ ONLY".
I even checked properties of this database attached, it was not read only. Please suggest.
Thanks in advance,
Ankit Jain
ankitj
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
