Recently i got one assignment from my senior, I have to merge multiple sql server databases,
Where to start?
How to start?
What prerequisites should consider?
What factor will cause error, how to tackle that error.
Hi all,
I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:
declare @num_genhistory_rows int,
@num_contents_rows int,
@num_tombstone_rows int
declare @retcode smallint
--select count(*) from msmerge_contents
-- records before 2,633,848
exec @retcode = sys.sp_mergemetadataretentioncleanup @num_genhistory_rows OUTPUT , @num_contents_rows OUTPUT , @num_tombstone_rows OUTPUT
select retcode =@retcode
select num_genhistory_rows =@num_genhistory_rows
select num_contents_rows=@num_contents_rows
select num_tombstone_rows=@num_tombstone_rows
--select count(*) from msmerge_contents
-- records after 2,633,8
Results :
retcode
0
num_genhistory_rows
0
num_contents_rows
0
num_tombstone_rows
0
Has omebody any idea why this is not working ?
I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :
declare @curdate datetime, @cutoffdate datetime
select @curdate = getdate()
select @cutoffdate = null
-- find max retention of all pubs the article belongs to.
-- add some safety margin to compensate for different clock speeds
select @cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @curdate))
from dbo.sysmergepublications where
pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)
select @cutoffdate
and this is the message error:
Msg 4121, Level 16, State 1, Line 7
Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.
I looked this function but I didn't find it.
any help will be appreciated !
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
...
Hi -
.
I need a way (Thru ASP code) to collect data from multiple database(s)..
Currently I have a 'Orders' table in 3 separate databases, would like to merge All to run a Query page.
.
Thanks - Dave
Hi All,
I have to merge few different databases to one big common database :) I mean about structure (tables, procedures, users, roles etc.) and data. May you suggest, help me how I should do this. How start? Create brand new database or relay on one old and add difference from other?
--
Regards,
anxcomp
I have two databases with each one on a different server. I can copyboth databases to the same server with no problems. But how do I mergethem?This is what I came up with so far but how do I do the same process formultiple rows and tables?IF NOT EXISTS (SELECT tmp_DB1.dbo.tb1.key1 FROM tmpDB1.dbo.tb1 WHEREtmp_DB1.dbo.tb1.key1 = tmp_DB2.dbo.tb1.key1)INSERT INTO tmp_DB1.dbo.tb1 (<all fields>)VALUES (<all fields>)Thanks in advance.
View 2 Replies View RelatedMy problem is this:
I have a database file on a hard disk. I have to read that data and merge it into a database on a sql server. How can you merge ? The databases are set up the exact same way. The only difference in them is the data.
Also, in the database file I have image names, I have to compare them to what is on the database on the sql server.
Thanks
I have 2 database, one has data and one is being created on my handheld. Is there a way to merge the database together so I get the data in the tables I need in the database being create or can I add tables to the existing database so I can use that for my testing?
here is the issue;
database 1 has tables with data - but does not have all the tables I need. - pre-existing
database 2 is being created when I start my app, and creates the tables I need but no data and not all the tables.
Is there a way to merge these to, so I don't have to create a huge insert statements for each tables I need data for?
or can I do a select from database 1 and insert the records into database 2?
example: insert into tbl1 (name) select database 2.table(col1) from tbl1, is this possible?
Hi,
I want to share knowledge that we have the abality to merge the two databases in one database. As if your site is already working with data base with out login page. And you are using asp.net built in funtion to create login page there will be a new database will automatically generated with the name of "ASPNETDB.MDF". you will check it in machine.config. If you want to get its data in old one database you only open sdk dos promt and write aspnet_regsql.exe and press enter. A wizard will open for you. in first two wizard you click next without changing it. and in the 3rd page you will enter the database name in which you want to write this ASPNETDB.MDF data. and in the final page you click finished button and close the sdk dos promt.
You should go on web. config file and modify the code for example.<connectionStrings>
<add name="wordexpress" connectionString="server= localhostsqlExpress;Database=wordexpress;Integrated Security=True"providerName="System.Data.sqlClient" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="server= localhostsqlExpress;Database=wordexpress;Integrated Security=True"providerName="System.Data.sqlClient" />
</connectionStrings>
And then check your old database you will see that there is new 11 table added.
My question is that I cannot get these table in my old database. I have working with the same procedure. Can any one tell me if there is any mistake from my side.
Naveed Baig.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
Hi gang,
We have a couple of databases on separate servers that have exactly the same schema, and we would like to set up merge replication between them.
Is that possible? The few times we've experimented on test databases, the subscriber database has been trashed and rebuilt using data from the publisher, rather than preserving data from both databases.
I'm really new to replication in general, so any kind of advice would be helpful.
Cheers,
Matt
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View RelatedHi all.
I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later time and replication was paused, but users continout to insert data indepentedly at the two servers.)
I need to get them up and running.
I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.
When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .
I so far managed to have merge replication running on two identical databases (schema and data).
Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes. I set the identity seed and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at puplisher and subscriber.) Is that ok? is that the way to do it?
Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.
Thanks allot for any help
Version.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:
Code SnippetCREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);
CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);
CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);
The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID column
to a unique field (as no doubt lots of people will have 1, 2, 3 in their
local databases and the global database must have a unique ID), and then
transfer this changed ID back to the local database? Furthermore, if it
changes the Sessions.ID column during the merge it will also need to update
the SessionDetail.SessionID foreign key to maintain referential integrity,
is this also handled?
So my question is, how much of this sync and data merge is automated and are
there any good examples or pointers for my scenario? I cannot reasonably use
a GUID for Session.ID as there will be lots of SessionDetail entries and the
size would be far too much.
Many thanks,
Chris
I want to merge these queries in one query. When I use UNION ALL parameter sth_tarih sort is wrong.
SELECT TOP 5 sth_stok_kod,sth_evrakno_seri,sth_evrakno_sira,cha_kod ,sth_RECno,sth_tarih
FROM STOK_HAREKETLERI AS SH INNER JOIN CARI_HESAP_HAREKETLERI AS CHH ON SH.sth_evrakno_sira= CHH.cha_evrakno_sira WHERE sth_stok_kod = (
SELECT sth_stok_kod FROM STOK_HAREKETLERI WHERE sth_RECno = (SELECT MAX (sth_RECno) FROM STOK_HAREKETLERI) ) AND sth_evraktip = 3
ORDER BY sth_stok_kod ASC,sth_tarih DESC
[code]....
Hi,
I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3
NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2
Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW
Thank you.
hi guys,
just wondering if there's a SSIS component out there some what similar to Merge join but can take up more than two inputs to join.
basically i have a big package with data sources coming from everywhere, they all have a unique column i can join on, so right now, i use merge join for every two sources, then join the output of that to another source so on and so forth. it would be easier if i can just join all of the sources in one component rather than putting a merge join for every single join. is there such a component out there, custom built maybe?
cheers
I have 1 publisher in MI and 3 pull subscribers (1 local subscriber in MI and 2 in NC).
I am trying to decide whether to use a central distributor or a distributor per subscriber. It is my understanding that if I use a central distributor, large transactions that are being process will block other transactions from happening and increase latency. Most of the transaction will be relatively small, but several times during the day clients update large amounts that cannot be predicted. I want to make sure the other locations are not waiting on a large update to be processed through the distributor.
My current plan is to create subscribers with their own distributor. What are the issues with doing that over a central distributor?
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[code]....
I have a set of data which contains individual logon and logoff data. The table is as follows:
AGENTID, EVENTTIME, CURRENTSTATE
1234, 2015-05-12, 15 (For Logon) or 25 (For Logoff)
I'm hoping to extract this data as follows:
AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION
Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
View 12 Replies View RelatedHi All
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
regards
sasan
Can we insert into multiple table using merge statement?I'm using SQL Server 2008 R2 and below is my MERGE query...
-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.
WITH Cont as
( Select ContactID from Contact where ContactID=@ContactID)
MERGE Employee as NewEmp
Using Cont as con
[code]...
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
Hey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies View Related