Stored Procedure Doesn't Recompile After Replication Updates Underlying Tables
Mar 21, 2007
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
View 3 Replies
ADVERTISEMENT
Jun 15, 2000
How to recompile a stored procedure automatically in SQL server 6.5?
Thanks in advance
Stella
View 1 Replies
View Related
Jun 15, 2000
Does the SQL Server 6.5 system recompile all stored procedures automatically when the server is restarted?
Thanks.
Stella
View 1 Replies
View Related
Aug 20, 2004
Hi,
currently i am working on performance tuning on some stored procedure and found that most of the stored procedure include with recompile on top of it.
i try to remove it and now it improve a lot on speed tuning. However, for those stored procedure which is using dynamic sql, is it a must to include recompile in our stored procedure?
View 10 Replies
View Related
May 17, 2007
Hey all,
We have a problem with one of our MS SQL 2000 databases and some stored procedures.
I'm not sure exactly what the problem is, but these are the symptons....
The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.
Calling it through the query analyzer it runs within a second.
Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.
We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.
I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.
The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.
The code for the second results set is...
Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate
From Pricing p
Inner Join Security s
On p.SecurityID = s.SecurityID
Left Outer Join Issuer i
On s.IssuerID = i.IssuerID
WHERE p.PricingTypeID = @PricingType
And p.TenorTypeID = @TenorType
And p.CaptureDate Between @DateFrom And @DateTo
AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
Does anyone have any idea what might be going on here?
Regards
Shaun
View 13 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
View 4 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Apr 27, 2005
I have the following stored procedure:
CREATE PROCEDURE user1122500.sp_modifyOrganization(@Name nvarchar(100),@Location nvarchar(50),@Url nvarchar (250),@Org_Type nvarchar (50),@Par_Org_Id uniqueidentifier,@Row_Id uniqueidentifier,@Error_Code int OUTPUT,@Error_Text nvarchar(768) OUTPUT)ASDECLARE @errorMsg nvarchar(512)DECLARE @spName sysname
SELECT @spName = Object_Name(@@ProcID)SET @Error_Code = 0
IF @Url > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET URL = @Url ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'URL', @Url SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Org_Type > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET ORG_TYPE = @Org_Type ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'ORG_TYPE', @Org_Type SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Par_Org_Id IS NOT NULL BEGIN UPDATE USER1122500.ORGANIZATION SET PAR_ORG_ID = @Par_Org_Id ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Par_Org_Id SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Name > ' ' OR @Location > ' ' BEGIN
IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @Name AND LOCATION = @Location) BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'NAME', @Name, N'LOCATION', @Location SET @Error_Code = 55004 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code)-- RAISERROR(@Error_Text, 10, 1, @spName, @Error_Code, 'ORGANIZATION', @errorMsg) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'sp_name',@spName)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_cd',@Error_Code)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'tbl_name','ORGANIZATION')) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_msg',@errorMsg)) RETURN(@Error_Code) END
IF @Name > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET NAME = @Name ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Name SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Location > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET LOCATION = @Location ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'LOCATION', @Location SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
ENDGO
This is the code that runs it:
string strSP = "sp_modifyOrganization";
SqlParameter[] Params = new SqlParameter;
string strParOrgID = null;
if (this.ddlParentOrg.SelectedItem.Value != "")
{
strParOrgID = this.ddlParentOrg.SelectedItem.Value;
}
Params[0] = new SqlParameter("@Name", txtName.Text);
Params[1] = new SqlParameter("@Location", this.txtLocation.Text);
Params[2] = new SqlParameter("@Url", this.txtURL.Text);
Params[3] = new SqlParameter("@Org_Type", this.txtOrgType.Text);
//Params[4] = new SqlParameter("@Par_Org_Id", strParOrgID);
Params[4] = new SqlParameter("@Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());
Params[5] = new SqlParameter("@Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());
// Params[5] = new SqlParameter("@Row_Id", lblOrg_ID.Text);
Params = new SqlParameter("@Error_Code", -1);
Params[7] = new SqlParameter("@Error_Text", "");
Params[4].SqlDbType = SqlDbType.UniqueIdentifier;
Params[5].SqlDbType = SqlDbType.UniqueIdentifier;
Params.Direction = ParameterDirection.Output;
Params[7].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
if (Params.Value.ToString() != "0")
{
lblError.Text = "There was an error: " + Params.Value.ToString()+ "###" + Params[7].Value.ToString();
lblError.Visible = true;
}
}
//catch (System.Data.SqlClient.SqlException ex)
catch (System.InvalidCastException inv)
{
lblError.Text = lblOrg_ID.Text + "<br><br>" + inv.ToString() + inv.Message + inv.StackTrace + inv.HelpLink;
lblError.Visible = true;
}
catch (Exception ex)
{
lblError.Text = lblOrg_ID.Text + "<br><br>" + ex.ToString();
lblError.Visible = true;
// return false;
}
This is the exception being generated:
System.InvalidCastException: Invalid cast from System.String to System.Guid. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:Program Files\_vsNETAddOnsMicrosoft Application Blocks for .NETData Access v2CodeVBMicrosoft.ApplicationBlocks.DataSQLHelper.vb:line 542 at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:Program Files\_vsNETAddOnsMicrosoft Application Blocks for .NETData Access v2CodeVBMicrosoft.ApplicationBlocks.DataSQLHelper.vb:line 458 at development.youthleadercert.com.share.ascx.organizationForm.btnAdd_Click(Object sender, EventArgs e) in c:documents and settingsmark rubinvswebcachedevelopment.youthleadercert.comshareascxorganizationform.ascx.cs:line 352
I have no idea what field is even causing the error, nor do I see that I'm even using a GUID field. I've been stuck on this for 2 days. Any help?
View 2 Replies
View Related
Jan 5, 2008
Hi dear users,
I have created procedure which main function is to update row of the table. Below, there is SQL code of this procedure.
Code Block
CREATE PROCEDURE Zakl_UpdateRow
@zakl_id INT,
@zakl_nazwa VARCHAR(25),
@zakl_miasto VARCHAR(20),
@zakl_ulica VARCHAR(30)
AS
UPDATE Zaklady SET
Zakl_Nazwa=@zakl_nazwa,
Zakl_Miasto=@zakl_miasto,
Zakl_Ulica=@zakl_ulica
WHERE Zakl_ID=@zakl_id
When I execute this procedure I have to give all declared variables: "@zakl_id", "@zakl_nazwa" and so on. Sometimes I don't want to update all columns in the row but only one f.ex name of the street ("Zakl_Ulica"). And I want other colums of the row to stay the same ("Zakl_Nazwa","Zakl_Miasto"). There is no point in rewriting values of "Zakl_Nazwa","Zakl_Miasto" as I want only to change value of "Zakl_Ulica".
this is execution clause
Code Block
EXECUTE Zakl_UpdateRow @zakl_id=70, @zakl_ulica='kosciuszki 7'
If I don't ascribe value to the variables (@zakl_nazwa, @zakl_miasto ), the columns in the row ("Zakl_Nazwa","Zakl_Miasto") should stay unchanged.
So what should I modify in my procedure, to have it working?
Best Regards
Maciej
View 3 Replies
View Related
May 27, 2014
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies
View Related
May 9, 2006
I'm rather new at reporting services, so bear with me on this one.
I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@startWeek and @endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@startWeek", which was not supplied".
I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.
Any help would be greatly appreciated!
View 3 Replies
View Related
May 10, 2006
T-SQL Debugger Doesn't Allow Stepping Through Stored ProceduresAnd there is no other procedure with the same name owned by dbo or anyother users.There is no error messages also, it just completes procedure andreturns resultserver:Microsoft SQL Server 2000 - 8.00.818 (Intel X86)Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build2195: Service Pack 4) and I don't know how do determine Client'sversion.What should be done (on client site or server site )to fix thisproblem.Thank you
View 12 Replies
View Related
Jan 14, 2008
I'm having some very frustrating 'syncing' problems with my report (currently in dev in visual studio) and what actually exists in the database. To start, I had my report definition open, and realized I needed a new sproc. So I went over to the db and created a new sproc, lets call it "Test1". After making a new dataset in ssrs, I can't seem to get SSRS to realize the stored proc actually exists. I continually get the error:
"The stored procedure 'Test1' doesn't exist. (System.Data)
I've shut down visual studio, re-opened, etc. Same thing. It looks like visual studio has some cached version of sys.procedures that only refreshes when the bloody thing feels like it. Any ideas?? I've been doing this for awhile, and I've minded the connection string, correct sproc syntax, etc. It runs fine on SQL Management studio.
Another, similar problem - When adding in a new column to a sproc, the dataset doesn't seem to pick up on it, no matter how many times the "refresh fields" button is hit.
View 1 Replies
View Related
Mar 9, 2007
Hi,
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
View 3 Replies
View Related
Apr 9, 2007
I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know
View 5 Replies
View Related
Feb 20, 2008
I have a stored procedure with the following:
CREATE TABLE #t1 (... ...);
WITH temp AS (....)
INSERT INTO #t1
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
Thanks.
View 5 Replies
View Related
Feb 4, 2008
Hi All,
I Have a nifty little stored procedure that takes data from an ASP form post
and inserts the data into two tables. However what I really need this to do
before the insert is check whether there is already a record that matches
some of the criteria, and if so returns an error "This Username already
exists" and only if there isn't a record that matches the criteria is the
record inserted.
my current stored procedure looks like this -
Code Snippet@siteid int,@companyname nvarchar(50),@address nvarchar(500),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(225),@url nvarchar(225),@companytype nvarchar(50),@billingcontact nvarchar(50),@name nvarchar(50),@AccountType nvarchar(50),@PASSWORD nvarchar(50),@AccountLive nvarchar(50),@EmployeeLevel nvarchar(50)ASDeclare @NewID INTINSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)SELECT @NewID = SCOPE_IDENTITY()INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone)
The values that i need to check against are -
@siteid
@email
in the table dbo.JBEmployee against columns JBESiteID & JBEUsername
What i would really like to do if a record exists is return the user to an
ASP page, which contains all of the variables previously enterred -,
@siteid int,
@companyname nvarchar(50),
@address nvarchar(500),
@phone nvarchar(50),
@fax nvarchar(50),
@email nvarchar(225),
@url nvarchar(225),
@companytype nvarchar(50),
@billingcontact nvarchar(50),
@name nvarchar(50),
@AccountType nvarchar(50),
@PASSWORD nvarchar(50),
@AccountLive nvarchar(50),
@EmployeeLevel nvarchar(50)
together with a message that says "This Username already exists"
Does anyone have any idea how to do this???
Many thanks
View 6 Replies
View Related
Jul 23, 2005
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies
View Related
Nov 12, 2015
I have to write a report using SSRS using a cube somebody (no longer here) created. I know SSRS and SQL very well, but have never worked with cubes.
I want to make sure the cube was properly constructed (in terms of table/join relationships). I cannot find the tables/joins. I looked in SSMS under Data Source Views and everything else I could think of (or google).
Is it possible I don't have permissions?
View 2 Replies
View Related
Dec 3, 2001
I don't know if there is a fundamental problem with what I am trying to do, or am I just having problems setting it up correctly:
I have a SQL server multi-user database. I want my users to connect to this databsase via Access 2000 Data Project. No problem there. The database consists of one main table and several views (based on the office branch that the user works from). For example there is a Chicago view, an Atlanta view, etc. that all extract different records from the same underlying table. I need my users to have FULL ACCESS (select, update, delete) to their respective VIEWS, but they cannot have access to the underlying table. I've tried several configurations and I'm beginning to think that this may not be possible... is that the case?
If it is not possible to grant access to views but not the underlying table, then what are my other options? The objective is to have a multi-user table that each user "owns a piece of" without being able to see the tables or records belonging to their peers. Do I need to setup a table for every office, and somehow link those tables into one main table? How would I avoid duplicate records being entered into the separate tables? Any help would be GREATLY appreciated, as this problem has had me stumped for weeks.
View 2 Replies
View Related
Aug 10, 2000
If my data structure never changes, just the data itself, is
there a need to use the "with recompil option" on stored
procedures? Isn't there a performance hit having it in
the stored procedure?
Thanks
View 1 Replies
View Related
Aug 30, 2007
I know that all the documentation always tells you that sp_recompile will force a stored procedure to recompile the next time it is executed. However, I am not seeing the recompiles in a SQL Trace, when capturing SP: Recompile events. I have tried this on many different database servers, using sp_recompile and also the WITH RECOMPILE option when creating the proc.
Can anyone explain this?
View 4 Replies
View Related
Apr 11, 2001
We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.
View 4 Replies
View Related
Aug 1, 2006
I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:
Insert Error: Column name or number of supplied values does not match table definition.
The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.
The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.
Please advise how to slove this problem. Below is the part of the store procedure and the error message:
THANK YOU IN ADVANCE!
STOREd PROCEDURE:
ALTER PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]
@wsSTUAPP_WEXP_iID int
, @wsSTUAPP_iID int
, @wsSTUAPP_WEXP_iOrder int
, @wsSTUAPP_WEXP_sEmployerName varchar(75)
, @wsSTUAPP_WEXP_sJobTitle varchar(75)
, @wsSTUAPP_WEXP_sJobDESC varchar(360)
, @wsSTUAPP_WEXP_dStart datetime
, @wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@wsSTUAPP_iID
,@wsSTUAPP_WEXP_iOrder
,@wsSTUAPP_WEXP_sEmployerName
,@wsSTUAPP_WEXP_sJobTitle
,@wsSTUAPP_WEXP_sJobDESC
,@wsSTUAPP_WEXP_dStart
,@wsSTUAPP_WEXP_dEnd
)
END
ELSE
BEGIN
UPDATE tbl_wsSTUAPP_WEXP
SET wsSTUAPP_WEXP_iOrder= @wsSTUAPP_WEXP_iOrder
,wsSTUAPP_WEXP_sEmployerName= @wsSTUAPP_WEXP_sEmployerName
,wsSTUAPP_WEXP_sJobTitle= @wsSTUAPP_WEXP_sJobTitle
,wsSTUAPP_WEXP_sJobDESC= @wsSTUAPP_WEXP_sJobDESC
,wsSTUAPP_WEXP_dStart= @wsSTUAPP_WEXP_dStart
,wsSTUAPP_WEXP_dEnd= @wsSTUAPP_WEXP_dEnd
WHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iID
END
ERROR MESSAGE:
Command attempted:
CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@wsSTUAPP_WEXP_iID int
, @wsSTUAPP_iID int
, @wsSTUAPP_WEXP_iOrder int
, @wsSTUAPP_WEXP_sEmployerName varchar(75)
, @wsSTUAPP_WEXP_sJobTitle varchar(75)
, @wsSTUAPP_WEXP_sJobDESC varchar(360)
, @wsSTUAPP_WEXP_dStart datetime
, @wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@wsSTUAPP_iID
,@wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)
Error messages:
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
View 5 Replies
View Related
Apr 3, 2007
hi,
after reading docs, i always thought that when using merge replication, only at specific moments the whole thing was merged.
now when doing first tests, it seems that everything is merged immediately to my subscribers. is this normal?
View 1 Replies
View Related
May 4, 2006
We have merge replication running with anamous subscribers
We have generested lots of views tables and stored procedures like
sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
ctsv_07BA7383A12B4654B4D3A69B3053B227
aonflict_DH_tblReplicationRegion
How do we get rud of these I am concerned it will fill up the publisher database
Can any one advise
View 1 Replies
View Related
Feb 2, 2015
I have a table, which is being replicated with the identity column set "not for replication" and a stored procedure, which is also replicated (execution) which inserts into this table. When the execution of the stored procedure happens, the replication monitor complains about identity value not being provided.other than removing the stored procedure from replication?
View 0 Replies
View Related
Jul 27, 1998
Hi,
Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated?
When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.
If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.
What might be wrong?
-janne
View 1 Replies
View Related
Sep 26, 2006
I have an issue that is only occurring in a production
environment. The architecture is filtered merge replication between two SQL
Server 2000 SP4 databases. The publisher is standard edition and the subscriber
is personal edition.
The issue is that is I update certain rows on the subscriber the data is not
replicated to the publisher. Inserts and deletes seem to replicate correctly.
The issue is specific to certain rows in some tables. If I update other rows
the updates replicate correctly. The subscription has been re-initialized once
and it did not fix the issue. The replication process indicates that there was
not data to merge and the subscriber updates remain and are at that point
different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and
checking the publisher database. The synchronization process is executed using
the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an
earlier synch on this particular subscriber. We have other subscribing
databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue.
If anyone has any ideas or has seen this type of issue before I would appreciate
any direction to try to fix this issue.
Thanks
View 15 Replies
View Related
Oct 26, 2007
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
View 5 Replies
View Related
Jun 26, 2015
We have transactional replication publications from SQL 2014 to SQL 20122008 R2. When we add new tables to the existing publication and start the snapshot agent, Snapshot agent updates the statistics for random tables on the publisher DB. It appears that all the outdated statistics are being updated regardless which table is being initiated. In a highly transactional environment we can't update the statistics every time. its causing huge issues for us. Is there any work around for this ?
View 8 Replies
View Related
Oct 24, 2014
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
View 1 Replies
View Related
