Isssue With Repllication Accross Domains And Inter
Aug 10, 2007
Hi,
I am newbie in replication,
I have a requirement where need to set upo replication with db such as both dbs are in different domain.
I have created a merge publication with create pulblisher wizard.
I want to use FTP as default location for the snapshot so that from both the domain database can accesss this path and subscriber will take the snapshot from it.
Issue facing like to run the snapshot agent we need to provide windows domain user or machine user but we can't add this user in FTP machine as domain is differnt will not get access on this machine.
I am getting following error:
Message: The replication agent failed to create the directory '\192.168.7.57ftprootuncPARAG-TEST_NORTHWINDSUB_PARAG_PUB20070810185320'.
Stack: at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL, Error number: MSSQL_REPL52026)
Get help: http://help/MSSQL_REPL52026
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Logon failure: unknown user name or bad password.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory) (Source: mscorlib, Error number: 0)
Any help regarding this is appreciable.
Please can put more light on it if I am doing anything wrong.
Is it possible to configure transnational replication between two different domains also non trusted domains.
It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.
Hi,The tempdb db is having different collation than the application db.I rebuilt the master db with the appropriate collation after backing upmaster, model, msdb, appln databases.On restore of the databases (master, model, msdb, appln) from the backuprestores the backed up database collation.Is there any idea to restore the db with different collation than thebacked up collation.Thanks,*** Sent via Developersdex http://www.developersdex.com ***
here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU5152arrier and DeltaV Controller is XZZZZZQPD2X2NF0WIYPHUFQHB5OLU5153nets and field equipment interface. XZZZZZQPD2X2NF0WIYPHUFQHB5OLU5151This is the quote for RS3 migration
Basically I have Debt1 and Debt2 given and I need to calculate TotalDebtToDate As you see it contains sum of all debts from previous monthes(TotalDebtToDate from a row above) + current debt1 + current debt2
Is it possible to write such query in MS SQL 2005 for calculating TotalDebtToDate? please help!!! Thank you very much.
Basically I have Debt1 and Debt2 given and I need to calculate TotalDebtToDate As you see it contains sum of all debts from previous monthes(TotalDebtToDate from a row above) + current debt1 + current debt2
Is it possible to write such query in MS SQL 2005 for calculating TotalDebtToDate? please help!!! Thank you very much.
I need to update a field from another Table. The situation is that There are three tables in my db 1st have the Fields of NameID & classID & Books 2nd have the Fields of NameID & SectionID, 3RD have the Fields of SectionID
I want to add new field in the 3rd table named Books by setting NameID. kindly provide the script to addthe field.
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ
Are there any issues with running the 32 bit version of Windows 2003 server and SQL Server Standard edition on a 64 bit Intel hardware Platform? I don't see why there would be if the OS is 32bit but just thought I'd ask.
When you are pulling the data from PPV SELECT [MATERIAL] ,[IR_AMOUNT] FROM [LATCUBDAT].[dbo].[ppv] where [MATERIAL] = 'MR004' AND [IR_AMOUNT] = 53728.85 Result
material
IR_Amount
MR004
53728.85
When you want to join ppv to rmu and then pull the data then select R.[PRODL] ,P.[MATERIAL] ,P.[IR_AMOUNT] AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL] where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
When I use avg in the query I get this report select R.[PRODL] ,P.[MATERIAL] ,avg (P.[IR_AMOUNT]) AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL] where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85 GROUP BY R.[PRODL],P.[MATERIAL] ORDER BY R.[PRODL],P.[MATERIAL]
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
I will like to see
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
8954.808
32000
MR004
8954.808
36000
MR004
8954.808
50000
MR004
8954.808
52000
MR004
8954.808
54000
MR004
8954.808
If there where 4 product line then avg distributed between 4 and like wise
I have 1 table in my db that I have imported from a txt file using DTS.
In my database "DOJ" I have a table named "DOJGRAB" with ALL my data fresh from a import using DTS.
Also in "DOJ" are 6 other tables (NAME, PERSON, etc...)
How can I parse the columns out from my DOJGRAB table to fill existing columns in my other 6 tables? Basically a copy of specific columns from DOJGRAB to their proper places in the other tables.
Can somebody post an example of the syntax I would use to copy a column from one table to another? Thanks!
I have to DBs. One is the original and the other is for testing. They both have similar data structure. Wanted to know if there is a way to update tables contents in one db with data that is in the other one.
We have 4 clustered SQL2000 Servers each contains information specific to its application related to customer information in a casino player tracking database. My problem is as follows On the Playertracking database I can join and return information from the tables there with no problems the performance accross the decently sizable transactional based table is pretty decent. The problem is I need to filter this query down by the Type of machine the customer plays. The child key exists in the playertransaction table the parent key is on another server. Here is the lay out of the tables unecessary information from the tables were truncated for brevity.
CREATE TABLE [dbo].[PlayerSession] ( [PlayerId] [int] NOT NULL , [Mnum] [int] NOT NULL , [CoinIn] [money] NOT NULL , [CoinOut] [money] NOT NULL , [Games] [int] NOT NULL , [Jackpot] [money] NULL , [Win] [money] NULL , [TheoWin] [money] NOT NULL , [PlayerMod] [tinyint] NOT NULL ) ON [PRIMARY]
-- Player Demographics information CREATE TABLE [dbo].[Player] ( [PlayerId] [int] NOT NULL , [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FirstName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [MiddleName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SSN] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , ) ON [PRIMARY]
--Machine Information that links to Machine Type table
CREATE TABLE [dbo].[Machine] ( [MNum] [int] NOT NULL , [MachineTypeId] [smallint] NOT NULL , ) ON [PRIMARY]
-- Machine type code table
CREATE TABLE [dbo].[MachineType] ( [MachineTypeId] [smallint] NOT NULL , [Denom] [int] NOT NULL , [Par] [decimal](6, 2) NOT NULL , [GameType] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , ) ON [PRIMARY]
From the server where all the player information is ran I can query the linked database for the machine and machine type information like this.
SELECT m.MNum, mt.MachineTypeId, mt.GameType, mt.DisplayType FROM ACCTV.Accounting.dbo.Machine m INNER JOIN ACCTV.Accounting.dbo.MachineType mt ON m.MachineTypeId = mt.MachineTypeId
This is the information I am trying to get out but the query times out on me.
SELECT Player.PlayerId, Player.FirstName, Player.LastName, SUM(PlayerSession.CoinIn) AS sumCI, SUM(PlayerSession.CoinOut) AS SumCO,SUM(PlayerSession.TheoWin) AS SumTheo, AVG(PlayerSession.TheoWin) AS AvgTheo, SUM(PlayerSession.Win) AS SumWin, AVG(PlayerSession.Win) AS AvgWin, mt.GameType FROM Player INNER JOIN PlayerSession ON Player.PlayerId = PlayerSession.PlayerId INNER JOIN ACCT.Accounting.dbo.Machine M ON PlayerSession.Mnum = M.MNum INNER JOIN ACCT.Accounting.dbo.MachineType mt ON M.MachineTypeId = mt.MachineTypeId GROUP BY Player.PlayerId, Player.FirstName, Player.LastName, mt.GameType
The other option would be some sort of SubQuery but I dont know how to return results from the subqueries to the root query to be returned to the restulting recordset. I am not necessarily looking for an answer more of looking for a direction to go to find my solution.
"David Portas" <snipped for brevity> wrote:Example 1:[color=blue]>> UPDATE table_a> SET col = ? /* Unspecified */> WHERE EXISTS> (SELECT *> FROM table_b> WHERE table_b.key_col = table_a.key_col)>[/color]<snip again>Many thanks. I have used this sample extensively since you posted it. Hopeyou (or someone) can help me with one more thing: How would it be writtento update several fields in table A with data from table B, where as youhave shown, a column matches the records?TIA!~ Duane Phillips.
Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.
Is there a way to take a snapshot of a table at precisely the same time on multiple servers and write the table snapshot to another 'work' database? I'm asking in this forum hoping that perhaps SSIS has a built in tool to help with this. I'm wanting to audit table(s) accross seven different servers. In order to do this I want to make sure that I have the table at the same moment in time on all the servers. I plan on using the tablediff utility to then compare each table on each server against one another. If somebody knows a better method to do this please let me know.
We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.
Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:
I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.
Hello, Not sure if data minimg can help me solving this type of inquiries. Thanks in advance for any pointers you could provide me. Problem: We have many dimensions with many levels. Sales directors will sometimes note a drop or a spike when looking at one of the dimensions members, most of the time at a high level. Example, For Q4 07, the aggregation of Q4 Billing plus remaining Q4 Backlog dropped by $10M in America for the Automotive business from week 4 to week 5 while it was forecasted to remain stable. The immediate question that will be sent to the guys in charge of this business is "What happened?". Obviously evyone struggle to answer these questions eveytine there is a drop anywhere. Using classic tools, they will try to spot a single anomaly or determine if this is accross the board and what are the concurent factors like lower than expected shipments combined with some orders cancellations. The drop-off can lie in a single product or customer location, in which case it is fairly easy while time consuming to spot the anomaly using classic drill-down approach. The drop-off can also be more wide spread or caused by a combination of factors like unable to ship in time, orders cancellations and sales credits in which case it is more difficult to spot where is the anomaly and explain it. So I wonder if there is any way that I could use data mining to make the entire process of detecting anomalies accross dimensions levels, members and measures more straight-forward for end-users. What do you think? Thanks, Philippe
Hi, We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time. Is that copy process affect production time out?. Thanks,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Hey all, I need your help again..We are having many domains. Actually we have created SQLgroups in sql and granted permissions in our domain. But guys from other domains are just entering into their query analyser and accessing the sql server..They are not at all falling under our SQLgroups. We have given trusted connections between domains.. My question here is Is there anyway that there should be an option to select the domain before they log in thru query analyser? Like Nobody should enter from their domain ...only through our domain they should enter..? Please help me out..
Hi, I have two SQL servers in different domains(A and B), I cant use the Enterprise Manager to register eachother...they show me "Sever doesnt exist" but when I use ping I can show them.... Please help me.....What can I do?
I'm attempting to set up a dts transfer SQL 7 box to SQL 7 box. These two servers are on two separate NT domains with no trust relationship, and I will be sending the info across a VPN.
Anyone out there have a similar situation? Offer any recommendations, pitfalls, ports used, ways to do this??? I'd appreciate any ANY ideas on how to make this work. Thanks in advance. -Tricia
Is there any way to connect to SQL Server from a non trusted domain. Passthrough authentication works for other NT Server resources (like exchange folder, printers, shared folders), but SQL Server 7.0 does not seem to accept this passthrough authentication (where the username and password are the same in both domains). There is no internet access required.
I need to move three sql 6.5 servers over from one domain to another domain. What is the best way to do it and what should I look out for? I am really new at this so any help would be great.
Company with one head office and one remote office. In the two offices I have two domains with two PDCs. The two networks are connected with eachother through leased line and the routers are configured properly. The SQL Server is on the PDC in head office and "local" clients connect fine. I cannot connect from the remote office. I think that I have folowing solutions:
1. make trust relationships between two domains - it will be hard a little bit because second PDC is samba on linux
2. make all clients in remote office to be members of the head office domain - potential problems if the leased line drops
3. make all clients to log in with same account as SQL Server logs locally - stupid
I have a sql-table. The domain of one of my columns a fix number of string values (like "blue", "green" and "yellow"). However, there is a big likelyhood that the domain will be expanded by more string values in the future (however, none of the existing string values will ever be discluded from the domain). How would I go about enforcing that all strings added to my column in my table is within my domain?
Should I use a check constraint, or create a domain, or sholud a create another table with the domain values and use a foreign key? What would be best practise for my problem?
I not too hot on networking. When I type "IPCONFIG /ALL" the "Prmary DNS Suffix" is empty. Does this mean I have no domain? Can I configure a mirror on this server?
I would like to replicate databases from an SQL 2000 Server to a SQL 2005 Server.
a. First can this be done?
b. Does it matter if both servers are on different domains?
Secondly
If all is possible from my above questions, in order for me to enable replication on the SQL 2000 Server, i need to rename the server from LOCAL to a realname. I get an error message saying that it can not use nicknames.
a. If I go ahead and make a New SQL Server Registration, could this have a major impact on the applications using the Database server?
b. When renaming, do I first Delete the current server registration or should I first create the new one then delete the old one?
