Problems Replicating Text Values To Sybase ASE 12.5.3 !
Jan 15, 2007
Hi everybody,
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 ?
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
I have recently upgraded our Database server from 2005 Standard to 2008 R2 Standard.I am having a problem while replicating Full Text Index in New Infrastructure.
Full text Index was working fine in old infrastructure. Replication scenario for Old infrastructure Publisher: SQL Server 2005 Standard Distributer: SQL Server 2005 Standard Subscriber: SQL Server 2005 Express with Advance Services Replication scenario for New Infrastructure Publisher: SQL Server 2008R2 Standard Distributor: SQL Server 2008R2 Standard Subscriber: SQL Server 2005 Express with Advance Services/ SQL Server 2008R2 Standard
Whenever I try to replicate Full text Index by selecting "Copy Full Text Indexes"= "True" article property in Replication and create snapshot it will automatically set to "Copy Full Text Indexes"= "False" whenever I reopened publication properties or snapshot is created.Is SQL Server 2008 R2 Supports full text Index replication to SQL Server 2005.Do I missed some settings while setting up publication for Full Text Index.
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
Hi I'm using SSIS to export views & tables in SQL 2005 to csv files. My problem is that when I select that I want to use " as a text qualifier, it also uses them to surround non-text fields such as time/date and integer fields.
In SQL 2000 I used DTS packages and they handled the data without any issues.
I would like to get in the textbox all the cutomer name seperated by ",".
Is the same as I can use join(Parameters!CustomersName.Value,",") but I need to do that from the data set and not from the parameters since I don't have parameters for my customer name
I have a fulladdress column which holds the complete address with commas. i want to divide that into add1,add2,add3..... For eg: FullAddress is Primrose Cottage, Featherbed Lane, Wilmcote, Stratford-Upon-Avon, Warwickshire, CV37 0ER Then Add1 should be Primrose Cottage Add2 should be Featherbed Lane
Add3 should be Wilmcote Add4 should be Stratford-Upon-Avon
When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.
- With low volumnes of data, less that 50,000 rows, no problems
- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.
But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
Hi All I am working on a query to get all the datetime values in a column in a table into a comma separated text. eg. ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000 I wanted to get something like 2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
I'd like to create a report with the folloiwng format:
DATE1 DATE2 DATE3 DATE4 DATE5 [fixed 5 dates across the top, from today to T+5]
THING1 x x x x
THING2 x x x x
THING3 x x x x
THING4 x x x x
my raw data looks like this:
THING1, DATE1, TEXT VALUE 1
THING1, DATE2, TEXT VALUE 2
&c&c.
Now: there may be 0, 1 or several (by which I mean 2-5 max) text values to display at each intersection. If there are zero I'd like it to be blank, if there are one or several, i'd like to display them in a little list within the cell.
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle
(@ProductTitle varchar(255))
AS
SELECT ProductTitle
FROM tbl_Product
WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle) My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :) I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query. (tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server"> 2 <asp:ListItem Value="0">Open</asp:ListItem> 3 <asp:ListItem Value="1">Completed</asp:ListItem> 4 </asp:DropDownList> 5 6 7 <asp:DropDownList ID="drpdwnCompany" runat="server"> 8 <asp:ListItem Value="">All</asp:ListItem> 9 <asp:ListItem Value="cur">Cur District</asp:ListItem> 10 <asp:ListItem Value="jho">Jho District</asp:ListItem> 11 <asp:ListItem Value="sea">Sea District</asp:ListItem> 12 <asp:ListItem Value="san">Net District</asp:ListItem> 13 <asp:ListItem Value="sr">Research District</asp:ListItem> 14 </asp:DropDownList> 15 16 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>" 18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')"> 19 <SelectParameters> 20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" /> 21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" /> 22 </SelectParameters> 23 </asp:SqlDataSource> 24
I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster
The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)
The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.
I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.
The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?
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.
Hi, I was wondering if any SQL Server gurus out there could help me...I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(varbinary(max), CAST('test text' as nvarchar(max)))no results are ever returned.Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via CONVERT(varbinary(max), 'test text') then there is no problem, I get results as expected.
However if I populate the column via CONVERT(varbinary(max), CAST('test text' as nvarchar(max))) no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers, Gavin.
UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
Code Snippet
-- Create test database CREATE DATABASE FullTextTest GO USE FullTextTest GO
-- Create test data table CREATE TABLE TestTable ( pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY, varbinarycol VARBINARY(MAX), documentExtension VARCHAR(5), ) GO
-- The below single entry WILL BE FOUND (the text source is being entered directly) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html') INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html') GO
-- Create the full text catalog sp_fulltext_database 'enable' GO CREATE FULLTEXT CATALOG TEST AS DEFAULT GO CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033) KEY INDEX tablePK GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
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.
