SQL 2012 :: Compare Database Across Servers
May 28, 2014I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies
ADVERTISEMENT
SQL 2012 :: How To Compare Settings Of Two DB Servers
Sep 24, 2014I am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
View 2 Replies View RelatedSQL 2012 :: Split Database Across Servers
Apr 4, 2014I would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies View RelatedSQL 2012 :: Database Project Schema Compare Fails To Pull In CDC Tables
Jul 11, 2014I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
SQL 2012 :: Multiple Database Servers - One Instance Name
Apr 24, 2014Scenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.
Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.
So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.
Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.
There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.
I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.
Compare Tables Of Different Servers
Oct 8, 2014I 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?
Compare Databases Between 2 SQL Servers
May 17, 2006Hello,I wish to see if the tables from database A server A1 exist in databaseA on server B1.I setup a linked server from my first server (robertcamarda) to atarget (cognos-dev)This works from robertcamarda:select count(*) from [cognos-dev].ds_v6_source.dbo.stdmasand this works:select * from ds_v6_source.information_schema.tables order bytable_namebut this wont work:select * from [cognos-dev].ds_v6_source.information_schema.tables orderby table_nameError:Msg 117, Level 15, State 1, Line 1The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'contains more than the maximum number of prefixes. The maximum is 3.I want to do something like:select * from ds_v6_source.information_schema.tableswhere table_name not in (select table_name from[cognos-dev].ds_v6_source.information_schema.tables order bytable_name)so I can see of the sql server (robertcamarda) has any missing tablesthat exist on the server (cognos-dev)TIARobSQL Server 2005 Enterprise
View 3 Replies View RelatedHow To Compare Columns Of Two Tables In Two Different Servers
Mar 18, 2008
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
SQL Script To Compare Two Identical Tables On Two SQL Servers
May 23, 2007Hi 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
Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers
Aug 12, 2015I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
SQL 2012 :: Compare Two Columns In Three Tables
Dec 16, 2013I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.
View 9 Replies View RelatedSQL 2012 :: Compare And Join Databases
Feb 7, 2014A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.
I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.
SQL Server 2012 :: Compare Two XML Variables?
Mar 19, 2015I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
compare xmls by their "semantic value" ?
SQL 2012 :: DB Schema Compare Scripts
Apr 16, 2015I am looking for some SQL Scripts/tool to compare the two sql database and generate the difference results into Excel file. I am not looking for Sync/change scripts.
Example:
Result Type Desc
-----------------------
Col1 Column Added
Table1 Table Removed
SQL 2012 :: Schema Compare Tools
Jun 30, 2015Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
SQL 2012 :: Tool To Compare Result Sets
Jun 11, 2014I am looking for a tool to compare the result sets.
Is there any free tool or Microsoft built in tool that we can use to compare the result sets. Suppose if I change one thing in code and needs to test the am I getting the same result set or not? Instead of doing manually is there any way to compare both result set.
SQL Server 2012 :: Compare Column In Two Tables?
Apr 20, 2015i have two tables.
Table A
IdName
101Dante
102Henry
103Harold
104Arnold
Table B
NumberName
102Dante
107Gilbert
109Harold
110Arnold
106Susan
112Marian
I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.
Table C
Col1Col2
HenryTable A
Gilbert Table B
Susan Table B
Marian Table B
using below logic to get the values from tables..
select
t1.columnA
, t2.*
from
table1 t1
join table2 t2 on t2.columnB = t1.columnA
SQL Server 2012 :: Compare Tables With Count?
May 6, 2015using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex:
'Table A' -- 150
'Table B' -- 300
'Table A & Table B' -- 150
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
ELSE 'Table A & Table B'
END
FROM #tableA a
FULL JOIN #tableB b
ON a.name = b.name;
SQL Server 2012 :: How To Compare A List Of Values
Aug 3, 2015how would I compare a list of concrete values?
---table with items
SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items
-- table with categories
SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
[code]....
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
SQL Server 2012 :: Compare Two Different Columns Of 2 Different Rows In A Data Set?
Jan 29, 2014Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0
SQL 2012 :: Compare Two Similar Databases (A And B) Stored Procedure?
Dec 3, 2014Is there any way to compare two similar databases (A & B) stored procedure. I have to find stored procedure in second database B with respect to the difference.
View 7 Replies View RelatedSQL Server 2012 :: Compare Row Count Between Two Tables With 10k Rows?
Dec 18, 2014I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.
Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables
[code]....
SQL Server 2012 :: Compare Dates Between 2 Different Rows And Columns?
Feb 18, 2015What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies View RelatedSQL Server 2012 :: Data Compare To Identify Change
Mar 3, 2015I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');
SQL Server 2012 :: Compare A Table With Physical Path
Mar 31, 2015I have a table with two columns
id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG
And in a physical system there are more files than the table.
D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG
D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG
I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...
SQL 2012 :: Compare Two Tables A And B - Copy Matching Values To Table C
Mar 31, 2014I have 3 table
table_A
table_B
table_C
TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908
TABLE_B
SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766
I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C
EX-
SNO NAME ID
1 RAJU 070491
SQL Server 2012 :: Compare Characters Between 2 Strings And Eliminate Similarities
Oct 13, 2015I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.
Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.
I will give an example below to be more clear
--Start
declare @string1 as varchar(50)='imos'
declare @string2 as varchar(50)='nasos';
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
[Code] ....
The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas).
So the function should return 3 in previous example.
SQL 2012 :: How To Sync DB Between 2 Servers
Apr 23, 2014I have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedSQL Server 2012 :: How To Compare List Of Numbers Kind Of Like Lottery Results
Feb 5, 2015Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
SQL 2012 :: How To Represent Servers CPU Utilization
May 30, 2014I have multiple instances of SQL 2012 Std Edition on a 40 physical core server.What I have done is the use the Process - SQLServr -% Processor time Stat and divided by 16 ( the max number of Cores Std ed. can use) as a instance level measure. I also use processor object stats to show how busy the server is. How to represent the servers CPU utilization?
View 1 Replies View RelatedSQL 2012 :: Mirroring In Two Servers With Witness?
Jun 19, 2014One of my client asking mirroring between two server with witness.Is it possible witness and mirror in same server(with same instance)? OR Is it possible witness and primary in same server(with same instance) ? Best method to establishing mirroring with in two servers(with witness) ?
View 4 Replies View RelatedSQL 2012 :: Integration Services On Different Servers
Mar 26, 2015I have installed SQL Server on Server A. I have installed Integration Services on Server B.I found that you need to addto the integrations services MsDtsSrvr.ini.xml file the server for the database if you want to use MSDB package store. The issue I have now is how do I execute the jobs on the Integration Server. I dont want them to execute on the SQL Server. Is there a way to configure the SQL Server Agent jobs to execute the package on the other server? Do you have to setup windows scheduled tasks or some other tool to execute command line packages on the IS server?
I'm looking for any info on segregating these roles but still using the MSDB store for pacakages. I don't want SQL server installed on the IS server or IS on the SQL Server. I want the Integration Services server to do all processing... not database level obviously but flat file reads, web service calls, data manipulaiton, custom code, etc and just pass the input/output of data to the SQL server for storage or retrieval to offload the workload and segregreate responsibilities.
SQL 2012 :: MSDTC On Stand Alone Servers
May 28, 2015Does MSDTC auto-install with the plain vanilla version of SQL Enterprise? Or do I have to install it later?Do you know of any links that reference specifically SQL 2012 stand-alone server installs of MSDTC?
View 0 Replies View Related