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.
I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.
Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well
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?
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 RelatedDoes anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
View 2 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.
Hello
I have a production database that i need to refresh to our test environment daily. The database size is 700 MB.
I do not need to transfer the stored procedures and triggers , users and logins.
Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.
thanks
Setup:
I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:
From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B
In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT
The problem with this approach is that it's extremely slow for large datafiles (50M records each)
Questions:
1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?
Thank you
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View 10 Replies View RelatedI have 2 tables
Table Container
- ContainerID (PK)
- ContainerStartYear
- ContainerStartMonth
Table Item
- ITEMID (PK)
- VendorPartNumber
- ContainerID (FK)
- ITEMStartYear
- ITEMENDMonth
- PRICE
The idea is the you have many items in a container but you do not always have an item start date or container start date.My goal is to get the average price of a vendorpart in a month of the year.
So now I group all ITEMS by VendorPartNumber, ITEMStartYear, ITEMStartMonth. Averaging the PRICE of this group... BUT Now the hard part is that if the ITEMStartYEAR or ITEMStartMonth is null then we must group the item via the Containers Month. BUT in the case where we are missing a date on the Item and its container, we will omit this set from the query.
I have two tables in one table callhist there records for every contact made to a particular prospect and the resulting disposition. In the other table (contacts) there are the records for each prospect containing information about the prospect.
I need to results like this.
Here are the columns I need to Display.
Campaign Name(from Contacts Table)
Dials (Select count(*) from calhist (based on parameters))
Contacts(Select count(*) from calhist (based on parameters))
Pres(Select count(*) from calhist (based on parameters))
Sales(Select count(*) from calhist (based on parameters))
and this is the way I would like it to be displayed
Campaigname|Dials|Contacts|Pres|Sales
and Help would be appreciated.
Thanks
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
Hello To All @ Dev Shed,
I have three tables, say, A, B, and C. Both B and C contain/reference the primary key of A, ie B.a_id, and C.a_id. Multiple rows in B and multiple rows in C can have the same a_id. C and B have no relationships and essentially independant of each other.
I'm trying to find a single query (to prevent having to rewite function interfaces) that can return all the fields of A, the sum of a feilds in B, B.cost, where B.a_id = A.a_id and finially the count of C.a_id 's for where C.a_id = A.a_id.
The query I've been worlking on so far is as follows,
SELECT A.name, A.a_id, SUM (B.cost), COUNT ( DISTINCT C.a_id)
FROM A LEFT JOIN B USING (a_id) LEFT JOIN C USING (a_id)
GROUP BY A.a_id
This produces the correct result for the count of C.a_id (thanks to a DISTINCT) but the sum of B.cost is out by the factor of count C.a_id. I can see why this is happening but have completely run out of ideas. Group by on two columns may be? A strategically placed subquery? Is is even possible?
Any help would be much appreciated. Thanks in advance for your time.
Peter
PS I'm new to this forum thing so if you want the unsimplified query with all the full names just let me know and I'll post it.
Just ran the 6.5 to 7.0 upgrade. It put all tables into a 1 large file. Is there a way to move a single table out of the file and into a second filegroup?
View 1 Replies View Related