Hi there, actually I have a set of tables from my database model on SQL SERVER 2005 that I want to replicate (using merge replication) to a different server with SQL SERVER 2005 EXPRESS, Everything seems to be ok until I try so replicate just a portion of my model. I€™ve got some parent tables to replicate but I€™m not going to replicate some of their children, when I try to move them (just the parents and SOME children) to the other server using subscription, I have to erase some foreign keys (the children€™s foreign keys I wasn€™t supposed to migrate) in the target server to get my database migrated.
I am using Sql2000 and have 6 servers. On this 6 servers 4 servers have the same database
MY QUESTION is i need a script or advice that will help to do this:
Every time data chages in 1 of the 4 servers that have the same database, I want all changes to happen in the other 3 so that they must always have the same information
I want to replicate to an archive database. This means that the subscriber will have data that has been removed from the publisher. In my reading, I haven't seen any discussion of this specific scenario.
Here's what I imagine the solution might be:
EXEC sp_addpublication_snapshot @publication = N'My_Publication', @frequency_type = 1 -- only create the snapshot once GO
I set the publication snapshot to only execute once, that would be during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.
Here's my concern: aren't there times when you need to resync?
If you could push a new snapshot that dropped the tables on the subscriber and built the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.
After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
I need to implement SQL Reporting Services but do not wish to use the production database for running the reports against. I intend to set a seperate server for reporting services. The data only needs to be refreshed on a daily basis, during slient hours. What would be the best way to copy data from the production to the reporting services server?
How do you setup replication from SQL Server 2000 to a oracle database? We want to push changes from sql sever to oracle, evertime sql server table is changed. I have a working ODBC connection. When I try setting up a push to the oracle side I can not find the subscriber In the list. Can someone point me in a direction, with a detailed way of doing this.
I need to come up with a script that when executed it will create a stored procedure and trigger along with permissions. Is there a way to make this into a package. Any ideas?
Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day. Â Suspend Data Movement appears to be working perpetually without finishing. Â The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager. Â The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green. Â
The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state. Â There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?
I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,Â
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for  Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records. I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time. I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410. The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start. This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
Hello - I was wondering if anyone knew how to do this -
I have a database with a field for Id, LName, GName, DOB. In the LName field, some of the names have * placed after the names. Is there a way I can search for the entries in LName with the * in the record?
I have started a project using Linq for sql and SSCE. Everything goes well until I realize that SSCE can only run in full trust. Linq for sql is planned to support partial trust scenario. Any plans for such a support in Sql Compact Edition?
The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date. What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows... I appreciate any help, @PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END
I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field? Thanks for any help. Dennis
In our database we have the concept of 'Companies' each company has an entry in a tblCompanyControls using a field lCompanyNumber to uniquely identify it.
Company specific data is then grouped within additional tables using this lCompanyNumber. Thus all the departments for a particular company (eg 4) exist in a table tblDepartments with lCompanyNumber =4. The same applies for pensions, pay elements, employees and so on.
Each employee has various attributes stored in several tables, thus the main data is stored in tblEmployees with an lCompanyNumber to illustrate the company they belong to. And a system generated lUniqueID that is the 1 to many relationship to tables such as tblEmployeePayElements, tblEmployeePensionSchemes.
These in turn have a primary key that establishes a 1 to many with the period data for pay elements etc.
What we want to do is replicate only the data for a specific company, for tblEmployees, tblDepartments this looks straightforward as i just set a filter like 'tblEmployees.lCompanyNumber = 4'. My problem is how do I replicate just the tblEmployeePayElements for those employees that are in the specific company as the table does not contain the lCompanyNumber. Can you replicate a view? I an quite ignorant of replication and would really appreciate some pointers. Note DRI is not in use.
I am serious problem of partial update my query is something like bEGIN tRAN declare cursor...
SElect * from tableA where flag = null
open cursor fetch first...
while @@FETCH_STATUS = 0 BEGIN UPDATE TABLE tableB .. . . .
fetch next END CLOSE ... DEALLOCATE.. COMMIT TRAN
The problem is the cursor select retrieves says about 10000 rows, goes thru the loop for 1000 rows and just terminates without giving an error message,or rolling back in case of errors, but comes out as successfully completed.
I am at loss as what could be the problems.. any suggestions welcome..
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select * into #temp_2 from #temp_1 a, Server2.DBa.dbo.table2 r where r.field1r = a.field1a and r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific: r.field3r looks like 000884 a.field3a looks like 17445000884 So- I just want to pull in the 000884 off of a.field3a
etc like that. each row varies with one exception. administrator@sthou.com is in each one.
is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";" and then looping thru and updating each row?
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#, T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT# FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN ASTDTA.OEIND1 T02 ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN ASTDTA.OEINHDIH T03 ON T01.OHORD# = T03.IHORD# WHERE T01.OHOSTC = 'CL' AND T01.OHORDD >= 20120101 ORDER BY T01.OHORD# ASC
I have been asked to see what I can recover from a development server whose database became suspect during a power failure.
One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.
There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.
What are our options here?
Are their any good third-party tools to help us with this problem?
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
Hi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
I have a file where there is a partial row at the end. It doesn't cause an error, but I get a "partial row" warning during execution.
What do most people do with these partial rows? Do they just ignore them as long as they don't cause errors? Or is it better to handle the partial row with a conditional split, for example?
Just wondering what other people's thoughts on this are. I tend to be of the "get rid of it" camp, but maybe that's overkill? Just looking for opinions, best practices.
Hi - I am using partial backup & restore on a Data Warehouse database currently in development.
When I recently tested the restore procedure I got the following error when trying to an online restore of one of the ReadOnly filegroups:
Msg 3125, Level 16, State 1, Line 1
The database is using the simple recovery model. The data in the backup it is not consistent with the current state of the database. Restoring more data is required before recovery is possible. Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup.
I believe I received this message as the Filegroup I was attempting to restore had been set ReadWrite since it was backed up.
So - I am looking for a query to test that all my filegroup backups are consistent with the live database.
I think I can achieve this by checking the read_write_lsn & read_only_lsn values for the filegroup to restore are the same as the values in sys.master_files for the live database.
I am reading the lsn values for the backup from msdb.dbo.backupfile
Can anyone confirm this is the correct approach? or is there a better way to do this??
I have a question, I keep getting the warning where the lookup found duplicate reference key values when caching reference data. It also says to switch over to partial or no cache mode. I switched to partial and and it took way longer to run my package. I have around 200,000 records right now but this table will continue to grow. I guess my question is which mode should I be using? I have another table that will have millions of records in it.
How can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M"). Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol). All suggestions appreciated. Thanks Roger
Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
I'm testing mirroring in a high availability mode and getting some odd results. If I kill the SQL Service (or reboot the machine) fail-over works quickly and correctly. If I unplug the Network cable though I will get some of the databases failed over and some of the databases will either be Mirroring / In recovery or Principal / In recovery and never go live. Right clicking on them and going to properties lists there error that the database is unable to communicate with the Partner or Witness. The databases that this will occur on seem to move around (i.e. it isn't always the same databases, but will often be many of the same databases). If I reboot the databases that will not fail over tend to change fairly dramatically. I've seen instances where only 3 databases would fail over (I'm testing 17 databases) and other instances where all but 3 databases would fail over. I've had exactly one test where all databases failed over correctly. Again if I reboot a principal server, fail over works every time.
Has anyone else experienced only partial failovers for network issues or have a work around?
I have temporarily created a script that will force the databases to be the principal, but it's not great for high availability (probably will work for disaster recovery though).
Is it possible using Sql2000 to compare the first four characters of a parameter? WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)
