Hi, I have developed a software which is to run on multiple PCs on a network sharing the same database on an inhouse server. There is another database which has some similar tables that sites on a server where the website is. I need to have the common tables synchronized. How is this possible and what issues will I have in doing this?
Well there be a problem if the synchronization happens when someone is using our software which is making some changes to that table at the same time? Or if someone on the website is entering some information that updates the database?
I am using Visual Studio 2005 with C#. Access Database.
I'm writing a site in ASP.NET 2.0. I have business logic that contains two methods:ModifyUserInDB() // Modifies user data in a SQL 2005 tableandGetUserFromDB() // Gets user data from the SQL 2005 user tableIf the two methods above get executed at the same time by two different users (but on the same record in SQL 2005), how do I prevent the data from being corrupted? Does SQL do this automatically?If I make the methods static, and synchronize them, will it fix the issue? Are static objects the same instance for all users?
Hi,Is it possible to make changes to an Access 2000 database on my desktop, andhave it update those changes to SQL Server 2000 on the web?I have to update stock and prices every night. Right now I am using Access2000, and I just make the changes, and then upload the changed db to theserver, and overwrite the old db.But I am going to be using SQL Server 2000 soon, and I know you don't justoverwrite the db each time.There are about 6 fields that *might* change each night, per record.Is it called replication? How (basically) do I go about it? Are thereprograms out there that will make it easier?Thanks for any advice!J~
Hi,I have 2 tables (let's call them tableOld and tableNew) that I'm trying to synchronize. The tables may or may not be on the same server (I'm not sure - let's assume they're on different servers).Given below is the table structures:******************************************************************************* tableOld ------------- tableOld uses a View to access name information.View name = vwUserDetailsColumns in View = Firstname, Lastname, Fullname tableNew--------------- ProjectManagerID (PK, int, not null) <-- autogeneratedFName (varchar(50), null)LName (varchar(50), null)Inactive (int, null) *********************************************************************** Basically, I'm trying to write a script so that when a user runs the script that will do something like this: 1) it will add all the names in tableOld to tableNew (only if they don't already exist in tableNew)2) if there is a name in tableNew that does not exist in tableOld (because the name was deleted from tableOld), tableNew will set Inactive = 1 for that name. Any help is appreciated. Thanks.P.S. Is there a way the script can run automatically any time a user adds or deletes a name from tableOld?
I am trying to synchronize two tables on different servers, the one with me with the one is australia.
I have a table called MeriesHBMem on both databases and the one with me gets updated everyday, and then i have to use a program called SQL Delta to synchronize all the new rows. How can i go about doing this using SSIS?
Any help would be greatly appreciated! I though about using a trigger for this, but i don't know how to go about connecting the two servers in the trigger's SQL statement, and besides i also think that a trigger will be a huge performance issue.
Data gets inserted into the table, all i want is a SSIS package to execute every 30min or so and take the new data and insert it into the other database's table
Scenario regarding SQL 2000. I have the same table on 2 different servers, Server R and Server L.
This table schema is:
ID int not null Name varchar(50) not null Password varchar(50) not null IsUpdate int not null default (0)
People has an application that enables them to change their passwords in the table on Server L.
There is a for update trigger in the table on server L that sets IsUpdate column to 1 when any password has been modified.
The required scenario: - Synchronizing passwords in the table on server R with passwords in the table on server L. - Then setting the IsUpdate column in the table on server L to 0 again.
What is the best technology for that? Please send me a code proposal.
I have several situations where I have values stored in multiple tables (yes, I know that isn't a good practice but it is my reality) and I need to make sure that they stay consistent.
The problem is, they can be (and frequently are) updated from both locations.
I was going to use an update trigger on each to update the other but I can see that causing potential deadlocks.
Surely I am not the first person with this problem but I havent been able to find anything in the help files or on the forum.
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click Dim addtimestamp As String Dim f As String Dim z As String Dim g As String Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm") z = "C:Program FilesVSoftAppMissNewAppDB.mdb" g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.") If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then End If End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click Dim Filename As String Dim Restart1 As New RestoreRestart Dim overwrite As Boolean overwrite = True Dim xi As String
With OpenFileDialog1 .Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*" If .ShowDialog() = Windows.Forms.DialogResult.OK Then Filename = .FileName
'Strips restored database from the timestamp xi = "C:Program FilesVSoftAppMissNewAppDB.mdb" File.Copy(Filename, xi, overwrite) End If End With
'Notify user MsgBox("Data restored successfully")
Restart() If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then Application.Restart() End If End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click Dim L As New DatabaseEraseWarning Dim Cat As ADOX.Catalog Cat = New ADOX.Catalog Dim Restart2 As New NewDBRestart If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Exit Sub Else File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb") End If End If Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection 'Dim Cat As ADOX.Catalog Dim Tablename As ADOX.Table 'Taylor these according to your need - add so many column as you need. Dim col As ADOX.Column = New ADOX.Column Dim col1 As ADOX.Column = New ADOX.Column Dim col2 As ADOX.Column = New ADOX.Column Dim col3 As ADOX.Column = New ADOX.Column Dim col4 As ADOX.Column = New ADOX.Column Dim col5 As ADOX.Column = New ADOX.Column Dim col6 As ADOX.Column = New ADOX.Column Dim col7 As ADOX.Column = New ADOX.Column Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection Cat = New ADOX.Catalog Tablename = New ADOX.Table
'Open the connection Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want) Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType! col.Name = "ID" col.Type = ADOX.DataTypeEnum.adInteger col1.Name = "MA" col1.Type = ADOX.DataTypeEnum.adInteger col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable col2.Name = "FName" col2.Type = ADOX.DataTypeEnum.adVarWChar col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable col3.Name = "LName" col3.Type = ADOX.DataTypeEnum.adVarWChar col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable col4.Name = "DOB" col4.Type = ADOX.DataTypeEnum.adDate col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable col5.Name = "Gender" col5.Type = ADOX.DataTypeEnum.adVarWChar col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable col6.Name = "Phone1" col6.Type = ADOX.DataTypeEnum.adVarWChar col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable col7.Name = "Phone2" col7.Type = ADOX.DataTypeEnum.adVarWChar col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable col8.Name = "Notes" col8.Type = ADOX.DataTypeEnum.adVarWChar col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
'You have to append all your columns you have created above Tablename.Columns.Append(col) Tablename.Columns.Append(col1) Tablename.Columns.Append(col2) Tablename.Columns.Append(col3) Tablename.Columns.Append(col4) Tablename.Columns.Append(col5) Tablename.Columns.Append(col6) Tablename.Columns.Append(col7) Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection Cat.Tables.Append(Tablename)
'User notification ) MsgBox("A new empty database was created successfully")
'Restart application If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then Application.Restart() End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click Dim JRO As JRO.JetEngine JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name. JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted) File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme. Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification MsgBox("The database was compacted successfully")
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
I will have to setup two windows 2000 servers at separate locations. Each server will host the MS SQL 2000 database. The data on both database will need to be in sync in real time. Is it possible to do? If yes, any suggestions of how do do it?
Can someone recommend a utility (preferably open-source) tosynchronize changes across servers? I need to bring only data over insome cases, and only objects in other cases. Any ideas?Thx!
We have a database in an AlwaysOn Availability Group that has gone into a state of Not Synchronizing / Suspect on the secondary.
The reason why this happened is because the secondary ran out of disk space so the log file wasn't able to be written to. The database was set to synchronous mode.
Is the only way to recover from this to do a re-initialization or is there another way to recover?
I have some questions about the MSDN article "Step by Step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005" (it is msdn2.microsoft.com/en-us/library/Aa454892.aspx).
Everything works good up to Exercise 3: Synchronizing Data Between Any Backend Database and SQL Mobile Database Using a Web Service. It is said that we need a DataService.asmx file to be enetered into "Web reference name". Unfortunately I can't find the HOL302_SQL_Mobile to download and install this file. Where I can find this file?
Can anyone tell me how can I do such a replication(C#)?
How can I synchronise data from mobile device to SQL Server using GPRS connection? (I only know replicating over USB).
ISSUE: While synchronizing data between the PDA and MS SQL Server database, using Active Sync connection, the sync process fails at times and displays an empty error message box and occasionally it shows the following exception: Error During Synchronization: A native exception occurred. ExceptionCode:0xc0000005 ExceptionAddress:0x01627b28 Reading: 0x1e000000 OK to terminate CANCEL to debug Moreover the .SDF file on the PDA gets corrupted. The ActiveSync connection drops as soon as the sync process fails. Later when the .SDF file is deleted and restored the synchronization is successful. Note: The replication monitor in the SQL Server indicates that the synchronization was successful even when the above error occurs.
we canīt open most of the sql server tables from our Access project any more, which has to be related to collation or extended property problems.
trying to open any table will result in the information "The stored procedure has been executed but did not return any records" after the first try and later on nothing will happen any more; tables wonīt open.
basically this started to happen after recreating the sql database due to resolution 1 in Microsoft Knowledge Base Article - 318989: collation of the server is Latin1_General_CI_AS collation of the old database was SQL_Latin1_General_CP1_CI_AS, which led to the error described in the article. collation of the new database is set to <Database Standard>, now i have the problems described above.
anybody some good help on that?
ps: we canīt really rebuild the user database all the time, because we got flight data in about 4 billion records in there, so we will need anything quick and easy... thanks
Hey everyone. I've linke an access database and I am able to query the tables like so:
SELECT * FROM QFinity...tblEmployees
I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:
SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;
I get the error "Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo_evaluations'."
I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.
Hi All, I have recently moved jobs. From my last job I created a holiday database for the organisation, than I copied it on a storage device. Now, in my new job I would like to use same database. But the problem is most of the tables were linked. I know I can convert linked tables to local tables and I have tried it but it asks me for the new location with the same table names. Can some one please helpppppppppppppppppppppppppppppppppp I as I am really very desperate.
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK. Browse to the Access database file and connection set up---all good!!!
Dataflow task Add an OLE DB Source component Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list. I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
i am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
I am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.
Role Loans Payments Transactions Accounts Customer Emplo Database Admin SUID SUID SUID SUID SUID SUID Branch Manager SUI SUI SUI SUI SUI SUI Internal Auditor S S S S S S Loan Officer SUID SUI SUI S S Tellers S S S S SU Customers U
We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.
Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.
Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
Hi I have two servers, on one i have SQL Server 2000 ( root ) and on the second SQL Server 2005. I need synchronize some data ( not the whole database ) on 2005 but i can't use Analysis Services . What i can do in this situation ?
I have a SQL Server 2005 web database and a local SQL Server 2005 database. I want a tool that can synchronize both the database from the source (web) to the target (local).
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.Â
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
