Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.
I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx
Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @creation_script parameter of sp_addarticle
Here is the segment of the Publication Script referencing sp_addarticle:
exec sp_addarticle
@publication = N'mike_hayes_test_replication2',
@article = N'mike_hayes_test_replication',
@source_owner = N'bmssa',
@source_object = N'mike_hayes_test_replication',
@type = N'logbased',
@description = N'',
@creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@pre_creation_cmd = N'drop',
@schema_option = 0x00,
@identityrangemanagementoption = N'manual',
@destination_table = N'mike_hayes_test_replication2',
@destination_owner = N'bmssa',
@vertical_partition = N'false'
GO
I added the @creation_script value, and changed the schema_option to 0x00.
The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.
Any ideas?
Thanks for your help.
Mike Hayes
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 replicate text values from MS SQL Server 2005 to Sybase ASE 12.5.3 using replication of SQL Server 2005. The connection to the Sybase server is opened through a linked server using 'Sybase ASE OLE DB Provider' version 2.70.00.24. As Sybase ASE 12.5.3 doesn't support parameter of type text for stored procedures I used INSERT-, UPDATE- and DELETE-statements for 'statement delivery' in arcticle properties.
This works fine as long as the text attributes contains less then 16 K characters. Any longer text is truncated after 16384 characters.
The OLE DB connection itself is configured to use 1 MB for 'DefaultLength for Long Data'. So the provider should transfer more then 16384 characters. And direct update through the 'linked server' prove that.
Did anybody else recognize this limitation ? Is there a workaround which allows to replicate more then 16384 characteres ?
I'm built a trace in SQL Profiler and I have selected columns "DatabaseName" and "Filename" (among others) to be displayed. When I run the trace, the columns are displayed but the values are blank. The SQLServer 2K version is 8.0.760. Is this a bug or am I missing something?
I am running into an issue when adding data from multiple columns intoone alias:P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATIONIf one of the 3 values is blank, the value LOCATION becomes NULL. Howcan I inlcude any of the 3 values without LOCATION becoming NULL?Example, if ADDR1 and CITY have values but STATE is blank, I get aNULL statement for LOCATION. I still want it to show ADDR1 and CITYeven if STATE is blank.Thanks
I have a table that displays a bunch of data based on their percentage changes.
There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.
I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?
I have 3 paramaters@value1, @value2,@value3 being passed into a stored proc and each of these parameters can be blank. If one of them is blank and the rest of them have some valid values, then I should just exclude the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would do this select * from tblName where column1 like @value1 and column2 like @value2 and column3 like @value3
else if I have one of the parameter being passed as empty, I should ignore that parameter like if@value1 is empty then my sql should be
select * from tblName where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue. I want it through a stored procedure only.
Also, all the three columns can have null values in the table. Please let me know what is the best possible way to do this. Thanks in advance !.
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
Hi every one, I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value. can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
Hi, I have developed a report that uses oracle as the database. I have wrote my query by creating a dataset. In the query I am also calling a function. query: select EmpName, get_Client_Count(:cities), POS from ...
--function definition create or replace function get_Client_Count(cities in varchar2) return number is .... From the report I have multi selectable dropdown check box list for Cities.
the function returns count when I select only one city. Works fine with no problem. As soon as I select multiple cities I am getting the following error ORA-06553: PLS-306: wrong number or types of arguments in call to 'get_client_Count' I know this error was thrown since multiple values were selected.
Has anyone come across this situation and came up with workaround or solution to this? I really appreciate your inputs.
I am using the open query method to connect a Oracle server. Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows. I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem
SET QUOTED_IDENTIFIER OFF declare @sql varchar(750) select @sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER " + '")' EXEC (@SQL)
I'm 99% sure this is possible, but I wanted to confirm before I go upgrading one box in our replication scheme without having to do all the others (which are geographically dispersed):
Can an SP2 box replicate (merge replication in our case) with pre-SP2 servers? Most of our servers don't even have SP1 applied, and we're ready to upgrade, but I want to be sure I can do them one at a time rather than all at once.
I am trying to create an auto off-site backup of an entire database. This would include databases and users. It should also include changes made throughout the day.
Something challenging about this is I want it to also include design changes that may have been done throughout the day.
I understand log shipping or replication can deal with the data part of my solution. But how can I copy over the logins, users, and design changes?
Is it possible to have design changes replicated from publishers to subscribers?
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 posted a question about replicating logins to the database and the answer i got about doing a DTS to transfer logins is not good for me, is it possible to replicate syslogins tables so that i can do this if so, how because they are not listed Database and Publications when i try to create a publication, only individual created databases can be seen.
The reason for this is because when the DBA decides to change the user permission, i want the info to be merge replicated to the subscriber. At this moment i can run the DTS to transfer the login but it won't know when the logins have been updated and hence i won't know when to run it.
I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.
I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?
I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.
I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!
I am using Sql 2005 and merge replication. I am relying on the feature where schema changes are replicated to subscribers but I have come across a situation where schema changes stop being replicated.
This is the scenario:
I create a database and publish it for merge replication.
I add subscribers.
If I need to change the published database I can use ALTER TABLE ddl and the subscriber gets the changes.
If I have to add or remove a merge article as part of a database change I specify the @force_invalidate_snapshot=1, @force_reinit_subscription=1. No any ALTER TABLE statements following the article change will NOT be replicated.
Is this a known 'feature'? Is it because @force_reinit_subscription is set to 1?
We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.
I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.
would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?
I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...
My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?
My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:
EXEC sp_mergearticlecolumn
@publication = <publicationname>,
@article = <article name>,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
on each table I modifiy, after I have added the new column?
I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?
I use a merge replication between Sql Server and Sql Server Express.
When I enable a DB for .NET features (eg RoleManager), I have new tables and roles that are created and some GRANT are given on SPs.
When I replicate these DB to another one, none of my roles are replicated and I also loose my roles. Is there a way to replicate also the roles and the permissions ?
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query? Thanks.
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ BUTTONS:
OK ------------------------------ For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
