i have a publisher and a few suscribers running with sql 2003
I use automatic range handling to manage some identity columns in some tables
How can I to modify manually the value of the next range that the publisher will assign to the next suscriber that need a range?
This problem is about replication
I have a publisher and a few suscribers running with sql 2003
I use automatic range handling to manage some identity columns in some tables
How can I manually modify the value of the next range that the publisher will assign to the next suscriber that need a range?
sorry all help does not work , if the value like this
table = test
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
35 6 a 400
36 8 a 450
======================================
and other question is if magusageid is use int IDENTITY(1,1)
how can i edit char in my magusageid which like this
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
A000_35 6 a 400
A000_36 8 a 450
sorry the question is
when i insert one row into this table and the data type my want to auto increase 1 to z
in sql i can use IDENTITY this data type to increas but the column of data use only number without char
so i need to know how can i use IDENTITY + char to save data into one row
thank's
Hello all,
I am just beginning to get into replication and have a couple questions for the experts.
When I set up replication (transactional with queued updates), the default identity range was set to 10,000 for the publisher and 1,000 for the subscriber. I understand that if I try to insert 1,100 records at the subscriber, I will get an error saying that the identity range has been exhausted and I need to run sp_adjustpublisheridentityrange on the publisher and the Distribution Agent for the subscriber.
My questions are:
Why wouldn€™t someone increase the identity range to a very large number, just in case something happens and the publisher/subscriber link is broken for an extended period of time? What are the ramifications to a larger identity range?
Since identity range management is done by the distributor, what happens if the distributor goes down? Do the publisher and subscribers receive errors when they fill their current ranges?
Thanks,
Jarret
I need to import some data into a table that is in a merge bi-directional publication. The table has an identity column that is being "auto managed". The ID numbers I need to import are not within any of the assigned "ranges". They are less than any of the current ranges.
Turning ident_insert on does not work because of the check constraints error. The merge replication triggers validate the ID range as well.
Edit: using SQL 2005 standard publisher and subsciber. Publication is 2005.
Hi all i have setup merge replication with one publisher and 3 subscribers
The replication worked fine for about 2 months then i recieved the following error on all my subscriptions
The Publisher failed to allocate a new set of identity ranges for the subscription.
This can occur when a Publisher or a republishing Subscriber has run out of identity
ranges to allocate to its own Subscribers or when an identity column data type does
not support an additional identity range allocation. If a republishing Subscriber has
run out of identity ranges, synchronize the republishing Subscriber to obtain more
identity ranges before restarting the synchronization. If a Publisher runs out of identit
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
I have updated my sql server to service pack 2
The publisher is sql Standard edition and the subscribers are Express edition
Any help would be great
Thanx
I'm having a problem with merge replication. Â My publisher and subscriber are SQL 2008 SP1, and my distributor is SQL 2008 R2. Â Currently, there is no access to the subscriber data aside from the replication agents, so there are no data changes going on there, data is only being modified on the Publisher. Â My plan was to get everything setup and do some internal testing before releasing the subscriber for use.
View 4 Replies View RelatedHello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
Hi All;
I am trying to set up Merge Replication on a database and want to set the IdentityRange Management to Auto for all my tables which use a Identity column.
In the wizard, on Article Properties Page, I can do this by selecting a Table, and going for its properties, but this is a tedious task as I have ~300 tables to set this property on.
Is there another way or a global location where I can set the property to true and even mention the ranges and the threshold, so that I dont have to pick each table and set it individually.
I am also aware of the fact that I can Generate a Script and modify it and run that, but I was looking for some way to do this in the wizard.
Thanks!
I am using merge replication with automatic identity range management
I want to be able to give a disconnected subscriber a warning message if a certain percentage of the allocated subscriber identity range has been used
- so that they can make sure they reconnect soon to obtain a new identity range
I can get the current identity value OK, but I can't find a good way of finding the current permitted primary and secondary ranges
The information is available in the check constraint, but I don't really want to have to parse this!
CONSTRAINT [repl_identity_range_0DF907AC_40CF_4849_9BD0_2173C90A3805] CHECK NOT FOR REPLICATION (([ContactID]>(10000040000.)
AND [ContactID]<=(10000050000.) OR [ContactID]>(10000050000.) AND [ContactID]<=(10000060000.)))
I can't find any system view which will provide this information
Is there a recommended way of doing this?
Thanks
aero1
I have implemented replication between 2 servers (SERVER1 AND SERVER2) and for test purposes i just have 1 table(Table1) in the publisher.
View 1 Replies View RelatedI re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
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 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;
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View 6 Replies View RelatedI'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.
UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';
UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);
error message
(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:
NAME VAL
Jim 4
John 5
Tom 6
Julie 7
Rich 8
Rob 9
RANGE TO SELECT is 6-8 (inclusive)
Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)
NAME VAL
John 5
Tom 6
Julie 7
Rich 8
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedHi,
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
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
Is there a way to allow non-sysadmin to modify jobs they don't own from the management studio interface?
Even the most privileged SQLAgentOperatorRole does not allow this...
Thanks
The following was not written by me but I need to modify it and I am definitely no expert in SQL. It returns a list of files that a user is allowed to access based on whether or not they belong to a specific user group and that group has been granted access to the file.
Here is the SQL as it is currently:
SELECT a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, u.UserName, a.UserID, COUNT(ga.AnswerFileID) AS ShareCount
FROM
User AS u INNER JOIN
AnswerFile AS a LEFT OUTER JOIN
GroupAnswerFile AS ga LEFT OUTER JOIN
GroupUser AS gu
ON ga.GroupID = gu.GroupID ON a.ID = ga.AnswerFileID ON u.ID = a.UserID
GROUP BY a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, a.UserID, u.UserName, gu.UserID
HAVING (gu.UserID = 2)
UNION
SELECT a.ID, a.Title, a.Description, a.FileName, a.DateCreated, a.DateModified, u.UserName, a.UserID, 0 AS ShareCount
FROM AnswerFile AS a INNER JOIN User AS u ON a.UserID = u.ID
WHERE (a.UserID = 2) AND (a.ID NOT IN SELECT AnswerFileID FROM GroupAnswerFile))
I need to modify this code to add an additional check. I have added a file that allows specific users to be granted access to files. It is called AnswerFileUser and contains AnswerFileID (the file) and UserID (the user that has been given access). I need the query to return as part of the results those files where a user has been given access regardless of their group or group access. I hope this all makes sense. Thanks for your help!
Oops - this is SQL 2005...
give me answer and what's the difference between view and stored procedure
View 2 Replies View RelatedHow do you change the time out value for a specific connection? I'm having problems when I connect to SQL Server from my website and I want to test if this would help.
View 2 Replies View RelatedHow do I modify data in a sqlDataSource object (like inserting records, editing, deleting) similiary to how I modify data in a GradView using a DataList or repeater? I want to be able to have my own EditTemplate and be able to edit say four fields while I'm only viewing like two of them in the datalist/repeater.
<asp:DataList ID="DataList1" runat="server" DataKeyField="GameId"
DataSourceID="SqlDataSource1" RepeatColumns="1">
<ItemTemplate>
<%# Eval("field1") %><%# Eval("field2") %>....
</ItemTemplate>
<EditTemplate>......</EditTemplate>
</asp:DataList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT ...."></asp:Sql....>
When I'm trying to add a edit-button (<asp:Button runat="Server" ID="EditButton" Text="Edit" CommandName="Edit" />) and click it nothing happens....What is missing?
I have a DTS was created by another programmer which is for move online Database to a offline Database
and now I have to modify it
the DTS package
have 1 Source DB
and 63 distinateDB
between SourceDB and DistinateDB are "Black Line"
and almost of DistinateDB have a green line between a Task ( excute SQL work)
-- some of SQL work are Delete Table and some are Truncate Table
and now I have to add some limits for the DTS
1. First, move all the Data From online Database to Offline Database
2. to Delete Data from online DB , Online Database will only keep Data for 6 months ( older than 6 months have to delete except table A and table B)
can you help me? or tell me where have a reference information I can take a look... have no idea how to modify this DTS package..
thank you very much
my column UPdateTime , Type DateTime
UPdateTime
---------------------
2007-04-18 00:00:00.0002007-08-17 00:00:00.0002007-09-05 00:00:00.0002007-09-05 00:00:00.000
I write a Sql Query for select UPdateTime between 2007/09/01~ 2007/09/30
select * from ZT_Master where TDateTime < DateAdd(Month,-1,GetDate())
the result return all the column.. why?
thank you
I have the following query that produces this:
1 0 Arts & Entertainment6 1 Arts & EntertainmentDance2 1 Arts & EntertainmentMovies9 0 Automotive10 0 Business11 0 Cancer12 0 Communications3 0 Computers13 3 ComputersE-Learning4 3 ComputersHardware14 3 ComputersJava16 3 ComputersLink Popularity17 3 ComputersMicrosoft.net15 3 ComputersRSS5 3 ComputersSoftware7 0 Real Estate8 7 Real EstateFinance
Here is the query:
SELECT tblArticleCategory.ACategoryID, tblArticleCategory.AParentID, ISNULL(Parent.ParentCategory + '', '') + ISNULL(tblArticleCategory.ACategoryname, '') AS ACategory
FROM tblArticleCategory FULL OUTER JOIN(SELECT ACategoryName AS ParentCategory, ACategoryID
FROM tblArticleCategory AS tblArticleCategory_1
WHERE (AParentID =0) AND (AActive = 1)) AS Parent ON Parent.ACategoryID = tblArticleCategory.AParentID
WHERE (tblArticleCategory.AActive = 1)
ORDER BY ACategory
How do I modify this so that just the category name is returned and not the parent cetegory. In other words, everything shows up just once. Arts and Entertainment, once, then on the next line Dance, then Music then the next parent category which is automotive.
Here is the table:
ACategoryID int UncheckedACategoryName nvarchar(150) CheckedADescription nvarchar(300) CheckedAParentID int CheckedAActive bit Checked Unchecked
When you first come into the web page I want to exam the querystring and the modify the SQL Statement accordingly in a SQLDatasource.What is the right event of the SQLDataSource to modify the SQLDataSource.SelectCommand = MySQLString property?Thanks
View 3 Replies View Related