Hi All,
I am new to MSSQL Server 2000. I am sorry if you think that this is silly question.
What is the difference between identity – yes and yes(not for replication)
What are two different situations where I should use identity – yes or identity - yes(not for replication)
Thanks in advance
rainbow11
In my database table has auto Identity file which is (1,1) But Its Increasing 1000 Some time 100 I don't Understand why It is happening in my every table.
View 4 Replies View RelatedI had a strange problem today with one of the identity fields in a frequently used table. It appears that the Identity column for a table had stopped incrementing after it reached 2147483585. Since I had inherited this table, I am not sure if the identity column type has been modified from int to numeric, but the current type is Numeric (9) which is 19 precision and 0 scale value.
When resetting the seed to 1, it started working. I tried creating a temp table with numeric value and it increments well beyond billions with no problems.
Has anyone encountered this? Any best practices around defining Identity data type (ie. use int or bigint and avoide Numeric)?
Thanks
NS
Hi,
I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').
How do I handle in this case? Did any one come across such case?
Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?
If yes, how to do?
Thanks
mka
Hi
I am new to Sql server and had just finished the MErge Replication setup on one of the PRoduction server. Today I got the request to change one of the Datatype of one the Published Article. Please help as what are the correct step to make it happen. Is there any production downtime required and if yes , then how much.
Thanks in Advance
Sanjay
Sql Server 2000
I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.
Any thoughts on this is greatly appreciated.
Thanks in advance
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedHi 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 RelatedAfter 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,
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.
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
Hello,
I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, .....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,....)
When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2....PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.
The question is :
What is the best solution to generate a counter for each type?(With code sample)
Thanks.
Does the IDENTITY field type in SQL have a maximum size to it?
You know like int only goes so high up,
I've got a falrly simple task I'd like to achieve in SSIS without scripting if possible.
I have a table with three columns, Cust ID, Address and Group ID
I want to update the Group ID with a counter that increments every time the Address changes.
So currently the table looks like
Cust ID Address Group ID
62 ABC
57 DEF
79 DEF
42 GHI
11 JKL
20 JKL
and I want to end up with
Cust ID Address Group ID
62 ABC 1
57 DEF 2
79 DEF 2
42 GHI 3
11 JKL 4
20 JKL 4
I have created a view over this table that counts and Groups by address. This looks like
Count Address
1 ABC
2 DEF
1 GHI
2 JKL
What I'm thinking of doing is reading the View in Address order and updating the table (joining on Address) with a counter that increments for every row of the View read.
What I'm not sure of is how to increment this counter without creating a script. One thought I had was to create a new table from the view, and add an Identity column somehow (is this feasible in SSIS?)
Any advice appreciated
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
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 RelatedHi,
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..
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!
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 RelatedHi Everyone,
Can we use replication for two tables with identity columns
on SQL server 6.5 as well as 7.0
Thanks for your help
DP
I'we set up a replication between to sql 2000 servers(transactional replication), the problem is that user application is using select @@identity. I can se way this is a problem, but i cannot find an solution to this. Other than change the client app's code, which undesirable. Anyone with an suggestion how to solve this issue?
Regards,
Asbjorn :)
Hi,
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability
we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected
we need to store values as is
Error:
base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range.
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
at MS.Internal.MS
COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default);
DataRow dr;
DataTable dt = new DataTable();
DataColumn dc;
try
{
dc = dt.Columns.Add("Availability", typeof(decimal));
€¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal
€¦€¦€¦.
}
bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]";
bulkCopy.WriteToServer(dt);
thx
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
1. Can someone tell me how to replicate the identity field.
2. When I use the 'NOT FOR REPLICATION' option, I keeps getting synx error. Here is the command:
alter table tusers alter column {userid INT} IDENTITY (1, 1) [NOT FOR REPLICATION]. Do you know why?
3. When I use the transaction replication wizard, why some of the table do not allow me to replicate(There is a key and cross symbol on the articles selection)?
Thanks
(Long post)
I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.
The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
Dave
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
Thanks,
Brad
Dear all,
How does an identity is affected by merge replication? Can sql control it so that there will be no replication conflict?
Here's the scenario, I have several tables with an identity column. the database resides in a server in the head office, there are several remote sites and remote users which will be running sql 7 desktop edition. Each remote will download only the data subset he is allowed to. Each site will be allowed add new rows on several table. Then they will update the head office server through replication.
Thanks
Reden
hi there.
I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication,
I am receiving errors when I run Replication with Identity Columns,
Thanks in advance
Fin
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
View 5 Replies View RelatedHey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
View 6 Replies View Related I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
Sever: sql 2000
Replication: Merge
Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"
I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.
From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."
When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.
Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.
My question is what could cause the identity seed to get set to such a high number??
any thoughts would be great!