Does anyone have any idea how to syncronise two SQL identical tables across
a network, between servers?
We want to run our website from a SQL tables on the same server, but set up
so that if the main SQL tables across the network change, or the tables on
the web server change, the two seperate servers will
syncronise/update/trigger updates to the tables on each other?
We are running SQL server 2000.
AHA
John
Hello everyone,
Here's my situation...
I'm running a web service which involves 51 seperate servers and databases.
There are fifty licensee servers (One for each US state) and one corporate server.
Each night I need to upload sales and membership data from the licensee's databases to the corporate database to compile reports.
The application platform I'm using is ASP.NET 2.0 and the the database is SQL2005 express.
I want this process to be run automatically, so I believe it's a scheduled windows service I need to setup up in .NET to make the data transfers.
If anyone has already set something up like this, or knows the steps to take? I would love to have your input.
Thanks in advance,
Robert
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies View RelatedI have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
Any thoughts?
Hello,
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Thanks,
Mark
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
Hi!
I have a big database which need some separation
in multiple filegroups.
Therefore I plan to move tables to new filegroups.
As I can understand this must be done by
dropping the clustered index and
recreating it on new filegroup, then data will move.
1. best way to get a list of all clustered indexes (sys.indexes)?
2. how to get index scripts? (using SSMS, right click is manual task, but I need scripts for than 100 tables)
3. when having the scripts it should be easy to replace filegroup part
4. after movement: just shrinking old files?
Thanks for help ;-)
Hello.
I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.
Sincerely,
Computergirl
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement.
i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server.
</P>
<P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";</P>
<P>
Sql Server in Cerebrum: database dals
DalsData
ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag
123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1
124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | |
MySqlServer in Copernicus: database stat
Statuslog
ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%)
1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100
2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75
the output in datagrid should be:
ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag
123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1
124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | | could someone help me how would i do this?
Hi
I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server.
So what is the syntax to do it ?? hope it's possible...
Thanks,
Inon.
How to join tables on different servers if one server, for instance, SERVER2 required authentication?
View 2 Replies View RelatedI have two databases under two different servers.
Server1:
DB1
Table1
Server2:
DB2
Table2
I need to compare Table 1 & 2 using SQL server and how can I achieve this?
Hi, anybody can help me.How can i synchronize 2 tables on 2 different sql servers 2000i mean TABLE1(col1, col2, col3, col4) andTABLE1(col1, col2, col3, col4, col5, col6)the first 3 colums are the same in rwo tables.Thanks--Message posted via http://www.sqlmonster.com
View 6 Replies View RelatedWhat is the best way to use a table located on 1 server on anotherserver. We have an application that needs to use data from 2 separateservers. TIA.TS
View 2 Replies View Related
I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)
select @@ServerName, bs.server_Name, bs.Database_Name,
bmf.physical_Device_Name,
bs.backup_start_date, bs.backup_finish_date,
Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status,
Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success
from msdb.dbo.backupset bs
Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where type = 'd'
and bs.backup_set_id = (select max (bso.backup_set_id)
from msdb.dbo.backupset bso
where bso.server_name = bs.server_Name
and bso.Database_Name = bs.Database_Name
and bso.Type = 'D')
order by database_name
I have 2 tables with the following structure:
CREATE TABLE [dbo].[table1] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
[GrpRID] [int] IDENTITY (1, 1) NOT NULL ,
[GrpID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ,
[Status] [bit] NULL ,
[SortOrder] [int] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
I am transfering 2 table between 2 SQL server based on GrpRID from table2.
Since RID is identity in table1 sometimes it is different text for spesific
Rid in second server. Some how I need to get the match the right text from server1
to server2 and if text doesn't exists create a bew entry in table1 with the update to table2
wich should reflect correct RID.
I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before.Found this stored procedure
sp_addlinkedserver('servername').Would you just execute this and then run your query after the SP?
I have user that we just migrated his Access database to SQLServer. All went well with the migration, but then he came up withanother requirement to be able to replicate the database to a localSQL server living on the hard drive of a laptop. Before the migrationhe just copied the entire Access databse to the lap top.I tried using the Copy SQL Server Objects Task to move thenecessary tables from the production server to the laptop, but noticedit doesn't copy over the table Indexes/keys identiy fields etc. Iended up backing up the production database and restoring it to thelaptop database, but wondered if there is any way to move the tables,with their properties from one server to another? I know I can setup the backup process to run as scheduled, but the problem is the dataneeds to be moved on an irregular time table. I thought about justwriting code on the remaining Access front end to empty the localtables and then query the data from the production side to reloadthem, but I'm sure there's an easier way.Any suggestions would be appreciated.Thanks,Tom
View 1 Replies View Related
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
Hi people!!!
First I'll introduce my situation.
I have the folowing tables:
1.- Table "Codes", DataBase "COD", server 1001
2.- Table "Codes", DataBase "COD", server 1002
Both servers run SQL Server 2000 Edition.
What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.
I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.
Anyone can help me?
Thanks,
Bob
P.S.: Please!!!!!!!!!
Hi All,
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
Hope any one will reply soon.
Thanks,
lmp
Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.
Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?
I've run into the following problem using "select rowguidcol from [LinkedServer].[DBName].dbo.MyTable" (as opposed to using "select <rowguid column name>...".
Against a locally-connected database, the following SQL command works as expected:
SELECT rowguidcol from dbo.MyTable
However, If I run the same query against a linked server (using the 4-part notation), it fails with an 'Invalid column name 'rowguidcol'' error:
SELECT rowguidcol from [LinkedServer].[MyDB].dbo.MyTable
Msg 207, Level 16, State 1, Line 1
Invalid column name 'rowguidcol'.
I've searched around but can't seem to find any info that tells me definitively that I can't use rowguidcol against linked tables, so I'm wondering if I'm just doing something wrong?
Any ideas on how to get around this issue would be greatly appreciated. I really don't want to use actual column names, as this is for generated scripts.
More Info:
> I can select the data just fine from the linked server if I use the column name - so I don't think it's a configuration or permissions issue.
> The row I'm trying to view is, in fact, a rowguidcol. I used the exact same script to create the table on both the local (where select rowguidcol works) and the linked (where it doesn't) servers.
> Both servers are MS SQL 2005 Standard with SP2
Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.
I like to compare them and find and view the differences in records.
Thanks for any help.
Abrahim
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
I got two tables from 2 different server A and B.
I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"
I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"
i want to combine these two OLEDB sources with a resultset
"select ID, currencyNo, currencyName, exchangerate from table A , B
where A.currencyNo = B.CurrencyNo"
how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?
hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting
Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
Thanks
Hi all,
I have a query that I need to run where I join two tables that both
reside on different servers. I use an INNER JOIN statement to attempt
to join these tables, but for some reason I am getting the following
error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst
Hello All,
Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.
Many thanks in advance for any and all help,
Gurmi
I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?
exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'
FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"
Thank you
Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.