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?
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?
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,
sql server 7
i have a replication job going on for one particular table from one server to another
now i have added two new columns in source and replication works fine.
once i add these two new columns in destination the replication is failing.
i went to properties of the publisher and select the filter columns where i can find all the columns checked except the two new columns when i try to select these two its not responding pls help me in this.
pls help me in modifying this replication.
TIA
hastal
Dear all
I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?
Please solution.
Thank.
PC Kaveesin.
Hello,
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
Thank you
Salah
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 have a merge replication in place. I increased the identity_range to 100000 for a table. I have done this both ways, via the properties of the publication on SSMS, and via TSQL. I have call sp_adjustpublisheridentityrange. Then I recreated the sanpshot.  EXEC sp_adjustpublisheridentityrange @table_name = N'Label', @table_owner = N'caseup';but after synchronization, the range defined in the table's constraint has not changed and now all of the identity values are used up. All inserts are failing.What needs to be done to force the publisher to recreate the identity range on a subscriber(s).
View 3 Replies View RelatedI'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 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
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
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 RelatedHello ,
I have a problem with a publication which is a transactional replication on SQL Server 7.0 .
I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box
against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
Many thanks in advance.
Sandra .
Have a script that should extract data if :-
1) Mothly :-
-payday is equal to @today
-pay frequency is = 1
-payday between 1-31
2) Weekly
-payday is between 1-5 ( 1 =Monday,2 =Tuesday,3=Weds,4=Thurs,5=Friday)
-payfrequency is = 2
3) Fortnightly
-after two weeks
-payfrequency =3
How do l modify this procedure to be able to extract using the listed conditions ? Need help
CREATE Procedure Collections_Cats
AS
BEGIN
Declare @today int
Set @today = (SELECT Day(GETDATE()))
DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT distinct
n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr
INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today AND pay_sheet.pay_frequency =1
Order by l.loan_No
OPEN Collections_Cats_Cursor
-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END
CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO
Hi Guys,
I am using Sql server 2005, i am trying to find out all the tables that modified recently. i cant find any last modified date!!.
My situation is; i have altered so many tables, usually when i do alter i save the sql file for doing the same alteration in the production database. but somehow i lost the scripts and i am not sure what are the tables that i have modified and what is new.
i dont want to overwrite the whole database.
can anybody know how to get only the difference.
thanks
I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.
Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links
I want to modify trigger so that it generates new number only when it is not supplied in a insert command.
Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT IntAcControlCode FROM inserted)
UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc
Hi all,I have been left to modify an old unique index, which no longer works,as the two fields, which were used, are no longer unique. I’ve found twoothers that are unique. But as I’m new to this I need some advice first.What happens if one table inserts from another (the second containingthe index). However if there is a duplicate will the records which arenot duplicates be inserted? How could I insure this?ThanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies View RelatedI initially modified the column 'ID' from not allowing NULL to allow, and saved it. But now I will like to change it back to 'not allow'. But it gives me the following error message when I try to save my changes:
'Pages' table
- Unable to modify table.
Cannot insert the value NULL into column 'ID', table <databasename>.Tmp_Pages'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Is there a way I can work around this?
Using sql 2005
Hi
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
Let me know if I am not clear.
Thanks in advance.
HiI have developed a sql procedure for updating some xml content using SQL 2005 xQuery features.For example:Declare @xmlContent = '<books><book name="asp"/>< book name = "sql"/></books>'--Now I want to add attribute "book_id" to the xml content.SET @xmlContent.modify('insert attribute book_id{"1" } into (/books/book[@name="asp"])[1] ')SET @xmlContent.modify('insert attribute book_id{"2" } into (/books/book[@name="sql"])[1] ')SELECT @xmlContent--OUTPUT will be<books><book name="asp" book_id="1"/><book name="sql" book_id="2"/></books>QUESTIONQ1. Do we have any extension or plugin such that this SQl 2005 xquery feature can be incorporated in SQL 2000?Q2. If the answer for Q1. is no (as I expect), then do we have any TSQL feature in SQL 2000 for modifying xml content?I found all examples in SQl 2000 related with "openxml" function which performs the select kind of operations only. Like selecting data in XML format OR converting xml data in to tabular format. But I couldn't find any xml modification TSQL in SQl 2000 which can add element/attributes or change values.one workaround could be to consider xml content as string and use string functions to modify the content.But is there any more sensible approach available in SQL 2000?
View 1 Replies View RelatedHi,
Below is the SP that I want to modify it such that it does another thing apart from what its doing now. I want to to delete all the records corresponding to the BpDate value before this insert happens. by meaning deleting all the records, I meant to say delete all records where BpDate = @BpDate and SiteCode = @SiteCode.
CREATE PROC CabsSchedule_Save
@JulianDate smallint,
@SiteCode smallint,
@BpDate smallint,
@CalendarDay smallint,
@BillPeriod smallint,
@WorkDay smallint,
@CalDayBillRcvd varchar(30),
@Remarks varchar(50)
AS
INSERT INTO
CabsSchedule(JulianDate, SiteCode, BpDate, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @BpDate, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)
GO
Any help will be appreciated.
Thanks,