How To Alter The Data Type In A Replicated Databas

Jun 11, 2008

Dear All,
i've one database replicated from production server.
now i need to change one perticular table column datatype.
what steps i need to follow to do this?

thankyou very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 13 Replies


ADVERTISEMENT

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

Need To Alter Data Type On All Tables..

Jul 17, 2003

I need to alter datatypes from
numeric(18,0) to 'int' type across all the tables in the database of about 200 tables. Nearly 200 PK/FKs will be affected with this change..How can I do this with a script? Can anyone help witha script which drops all the constraints/indexs etc and then alters the datatype before re-creating the constraints/indexs..?

P.s. No rounding problem is expected as data is already in whole numbers.

Thanks,
Di.

View 1 Replies View Related

Alter Table Without Data Type

Nov 8, 2005

I would like to set a field to not null which I can do like this: ALTER TABLE table1 ALTER COLUMN field1 BIGINT NOT NULL

My question is there anyway to set this field to NOT NULL without having to specify the data type BIGINT? I'm using dynamic sql and if there was a way then I wouldn't need to go get the data type of the field dynamically and it would save me a step. Thanks.

View 4 Replies View Related

Alter Multiple Data Type

Apr 20, 2006

how do you alter multiple data type in a table?

in oracle we have "modify"
is there something similar in sql server?

View 7 Replies View Related

Alter Table In Replicated Database

Apr 26, 2003

How can I do an alter table in some table that replicated database ?
I got the error message when I try !

View 3 Replies View Related

Cannot Alter Column (column1) Because It Is Replicated

Jun 9, 2015

I am trying to change the size of the column which is the part of replication but its giving me the error "Cannot alter column 'Column1' because it is 'REPLICATED'.But same i tried to change for another column and its working. 

ALTER TABLE test1 ALTER COLUMN column1l varchar(80) NULL

whats going wrong .

View 3 Replies View Related

Unable ALTER Stored Proc If SP Is Replicated

Apr 12, 2008

HI,

My Stored Proc is Replicated to other DB Server, When iam trying to ALTER my modified SP in Publisher DB, ALTER statement not working, even it's not throwing any errors also.

My SP has very simple logic [Just for get data from DB] , no of lines 680, no of chars 32000 [Approx].

If i decrease the length of SP, it's working fine.


Can anybody help on this?

Thanks in advance..

View 2 Replies View Related

Taking Tables Data Of One Databas To Another Db

Sep 19, 2007

Hi,
I have two database.I want to import a table from database1 to database2 with table structure and data of that table.

Thank in Adavance.

View 3 Replies View Related

How To Modify Colum (alter Table) Of Table That Is Replicated?

Jul 20, 2005

Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern

View 1 Replies View Related

ALTER TABLE SWITCH PARTITION On A Replicated Table

Apr 10, 2007

A customer wants to implement table partitioning on a replicated table.



They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.



So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).



What are his architectural or practical solutions to using table partitioning and replication?











thx

View 5 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

Alter Column Type.

Jul 20, 2005

Hello,Easy one for the SQL experts.I have a simple table. For the example let's say it looks like this:CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)Now I want to alter this table and make the column column_a a floatinstead of an INT.How do I do that? I cannot DROP and ADD the column I would lose somevery precious data.Thanks a lot

View 1 Replies View Related

Alter Table Type Conversions With Default

Apr 29, 2008

I'm trying to write some code that will alter column to change its type, in this case from Nvarchar to Int, but I'm not sure if there will be any columns that are uncovertable (say, there's a string that does not have an Int value). In this case, I'd like it to default to 0. Is there a way to write a T-SQL statement to do this, or will I have to write an UPDATE stamement first to clear out any problem cases?

View 1 Replies View Related

Index Creation Causes Error The Conversion Of A Char Data Type To A Datetime Data Type Resulted...

Jul 23, 2005

Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

System.Data.SqlClient.SqlException: The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Dec 14, 2005

After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.

System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.

Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.

At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.

anyone having this problem before?? hopefully you guys can help.

Thanks,

View 4 Replies View Related

How To Tell When Data Has Been Replicated?

May 1, 2007

Hello,



In our environment (SQL 2005) we have a database that uses Transactional Replication to sync data between two SQL 2005 servers. There is a web app that reads/writes data to the publisher server and the other server (that gets the replicated data) is used by some other internal applications.

At times, there is a need to delete some data from the publisher server...but this can ONLY happen once the data has been successfully replicated to the second server. Is there any way to determine if a row has been replicated successfully?



thanks

- will

View 4 Replies View Related

Changes To Data Not Replicated

Jan 21, 2007

I have an application that uses web-based merge replication. My publisher is SQL 2005 and my subscriber is SQL 2005 Express. I control the replication with RMO code. If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases. However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio. What would cause these changes to not be recognized? Any ideas would be appreciated.

View 13 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS

Mar 7, 2007

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

View 3 Replies View Related

Size Of Data Replicated???

Jan 17, 2007

hi,

can anybody advice me how to find the size of the data replicated every minute. is there and procedure for getting the size of replicated data.



Thanks in advance

Jacx

View 1 Replies View Related

SQL Server 2008 :: Data Is Not Getting Replicated

Jun 24, 2015

We are using push subscription using transactional replication. Is there a recommended value for retention period on distributionDb? We are using default value of 72 hrs and recently we saw an issue where data was not replicated with an error that subscription was inactive. When I searched, I fid that it is related to the retention period setting on distribution DB.

View 0 Replies View Related

Manually Input Data Into Replicated Table

Jul 17, 2007

Is it possible to have a replicated table that you can open from and input data manually?

That is, I've got a table that I am replicating data to and I want to add some manual data to this table, every now and then.

Will this break replication?


www.beyonder422.com

View 4 Replies View Related

Measure Data Transfer Between Replicated Servers

Jul 20, 2005

Hello AllI was wondering if there's a way to monitor/measure data-transferbeing taking place between 2 serves in a replicated environment.I cannot see any counters, etc. to monitor this..??thanksSunit

View 1 Replies View Related

Last Time Publication/subscription Replicated Data

Jun 21, 2007

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??



I want to set up monitoring to make sure I am aware if something for some reason does not replicate



OH and it is on SQL 2005 enterprise... an

View 2 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type!!

May 13, 2008

hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
 
in my SQL database I have the date feild as datetime and the version as nvarchar(max)
this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient
Imports system.web.configuration

Partial Class Admin_emag_insert
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Record_DateTextBox.Text = DateTime.Now

End Sub

Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
Me.VersionTextBox.Text = ""
End Sub

Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click
Dim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim ConnectionString As String
Dim record_date As Date
Dim version As String
Dim emagSQL As String

'save form values in variables
record_date = Record_DateTextBox.Text
version = VersionTextBox.Text

ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString

'Create and open the connection
objConnection = New SqlConnection(ConnectionString)
objConnection.Open()
emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _
"values('" & record_date & "','" & version & "')"

'Create and execute the command
objDataCommand = New SqlCommand(emagSQL, objConnection)
objDataCommand.ExecuteNonQuery()
objConnection.Close()

AddMessage.Text = "A new emagazine was added successfully"

Me.VersionTextBox.Text = ""

End Sub
End Class
 

View 10 Replies View Related

Converting Numeric Data Type To Text Data Type

Jul 20, 2005

Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin

View 1 Replies View Related

Changing User Defined Data Type's Data Type

Sep 12, 2006

Hi,



I have a user defined data type which is called DEmployeeName,

it's length is varchar(20), how do i change it into varchar(30) without droping it?

I'm using SQL server 2005.

Thanks in advance..

View 1 Replies View Related

Convert Text Data Type To Smalldatetime Data Type

Oct 9, 2007

I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.


SELECT rsbirthday CAST(rsbirthday AS smalldatetime)

FROM panelists


the error returned is "incorrect syntax near 'rsbirthday'.

I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.

Thanks for the help!

View 10 Replies View Related

Replication :: Data Belonging To Specific Warehouse Replicated To Its Corresponding DB

Jul 10, 2015

We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS.  There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?

View 2 Replies View Related

Connect To SQL-Databas Over TCP/IP

Jan 29, 2007

Hi..

I am really new in C# Programming witf h Database. My problem is:

I have a SQL Database (in an other country) and My Application has to connect it for data transfer. How can I do that? Which alternatives do i have. Should I use a Web Service?

Thanks...



View 1 Replies View Related

Consolidation - Changing Replicated Data In A Central Subscribing Site

Sep 25, 2006

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

View 3 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved