.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Replication stored procedures not being passed parameters- default appears instead

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
Hi I am setting up transactional replication from Oracle to SQL Server, it is working fine for 9 tables, but for one it fails on updating and deleting with a message like this: Procedure or function 'spu_VGSM_SUBMISSION_CONTACT' expects parameter '@pkc1', which was not supplied. This is correct, in that the sp should be being passed this parameter, but when I do a trace it will show something like: exec "spd_VGSM_SUBMISSION_CONTACT" '12-3456-10','BLOGGS','S', '2010-07-01 12:43:43', 'M123456' default In other words, the old primary key values which should be being passed in are being replaced with "default". I am using the CALL syntax for the update sp. If anyone can suggest why this is I would be very grateful. This is the publication: exec sp_addpublication @publication = N'_Submission_Contact', @description = N'Transactional publication from Oracle Publisher.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'


View Complete Post


More Related Resource Links

Entity Framework and stored procedures with OUTPUT parameters (suggest API)

  
EF does handle stored procedures with OUTPUT parameters, but the generated methods are not the easiest to work with or safest you could ask for. The ObjectParameter objects you have to create, pass to the method and extract the value from are not type checked and reallyObjectParameter nameParam = new ObjectParameter( "Name", typeof(string)); ObjectParameter ageParam = new ObjectParameter( "Age", typeof(int)); context.GetPersonsNameAndAge( personId, nameParam, ageParam); string name = Convert.IsDBNull(nameParam.Value) ? null : (string)nameParam.Value; int? age = Convert.IsDBNull(ageParam.Value) ? null : (int?)ageParam.Value; is not what I would want to have to write.The ObjectParameter objects do make some sense for stored procedures that return both a resultset and some OUTPUT parameters, because in that case the OUTPUT parameters are only available after the resultset has been consumed and therefore you need some objects that will be populated after the ObjectResult<...> gets processed but for the stored procedures that do not return any resultset, there is no reason not to create the ObjectParameter objects within the method and extract the values there as well. So that the procedure above may be called asstring name; int? age; context.GetPersonsNameAndAge( personId, out name, out age); Things are not so simple for procedures returning both. I'm

using table valued parameters for stored procedures any limitations

  
I am thinking of using a table valued parameter for my new stored procedure, but wonder if there are any performance implications to be aware of and is the limitation of using statistics applicable with table value parameters, i.e do they keep statistics and will this affect the judgement of query plans i.e estimated number of rows, from my experience table variables do suffer from this problem but I would like to confirm if it applies to table value parameters as well.

How to call a Stored procedures with varchar output parameters in LINQ

  

I have a stored procedure with the following structure:

CREATE procedure EditFeriodigiesForm
(
@EditID int,
@producer varchar(50) output,
@IssuedDate datetime output,
@stadio varchar(50) output,
@Method varchar(50) output,
@Monades_N varchar(50) output,
@Monades_P varchar(50) output,
@Monades_K varchar(50) output,
@comments varchar(100) output,
@responsible varchar(50) output
)
AS
Begin Try
	Begin tran
	 
SELECT @IssuedDate=IssuedDate,@producer=ProducerID,@stadio=KalliergitikoStadio,@Method=Method,@Monades_N=Monades_N,@Monades_P=Monades_P,@Monades_K=Monades_K,@comments=Comments,@responsible=Responsible from ferti_odigies WHERE FertOdigiesID=@EditID
	
commit
end try
	
	Begin catch
	
	if @@error>0
		rollback
	
declare @ErrMsg varchar(4000),
		@ErrSeverity int
Select @ErrMsg=Error_Message(),
	   @ErrSeverity=ERROR_SEVERITY()

Raiserror(@ErrMsg ,@ErrSeverity,1)

end catch 

Now i need to call it in LINQ.I have done the mapping thing and the SPROC is ready to be called in my form.aspx.cs form, as following:
  int id = Edit_id;
            int? producer;
            DateTime date =Convert.ToDateTime(txtdate.Text);
            string stadio;
            string method="";
            string monadesn="";
            string monadesp="";
            string mo

Input and output parameters for stored procedures in VB.NET

  

I have several VB.NET functions which pass and receive values from executed stored procedures using parameters. However, the latest method I am using seems to be a little too specific on the datatype and data length of the parameters for my liking (i.e. LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource). For example:


Public Function LogError(ByVal strErrSource As String, ByVal strErrCode As String, ByVal strErrDesc As String) As String  
    Dim AerialConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("aerialConnectionString").ConnectionString)  
    Dim RetVal As SqlParameter  
 
    Try  
        AerialConnection.Open()  
        Dim LogActivityCommand = New SqlCommand("spLogError", AerialConnection)  
        LogActivityCommand.CommandType = CommandType.StoredProcedure  
 
        'Add input parameters  
        LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource &

Which jdbc version support table valued parameters to stored procedures?

  

Hi all,

I want to use table valued parameters in stored procedures; we have that in .NET client, so I do not want to write another version for jdbc client.

Does anyone know, which version of jdbc engine support that function?

Please give several lines of source codes.

Thanks a million,

 

 


Create Scripts creates invalid default values for CLR based Stored Procedures

  

Hey, Microsoft-Connect-Team,

why did you close this issue without even dealing with it??? This is a problem description!!


Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!


Pass parameters into multi stored procedures

  

Hello,

I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :

 

        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

Pass parameters into multi stored procedures

  

Hello,

I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :

 

        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

Transact-SQL Built-In Stored Procedures

  
To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

SqlDataSource how to change the stored procedures at run time

  

Hi

Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?

Regards

Dimitris


Test Run: Testing SQL Stored Procedures Using LINQ

  

Language Integrated Query makes lots of things easier. Here we put LINQ, or more specifically the LINQ to SQL provider, to use testing SQL stored procedures.

Dr. James McCaffrey

MSDN Magazine April 2008


Advanced T-SQL: Automate the Generation of Stored Procedures for Your Database

  

Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions. This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.

Peter W. DeBetta and J. Byer Hill

MSDN Magazine April 2003


.NET Reflection: Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET M

  

One major inconvenience of using SQL stored procedures from code is having to match type information. You have to write long wrapper functions around the procedures just to expose the data types. In the .NET Framework, however, the reflection services of the System.Reflection namespace allow you to query rich metadata that describe data types. This article explains how to use reflection in .NET to end your stored procedure nightmares. The author creates four custom classes, including one that generates a SQLCommand object based on the metadata of a method. Using this library, you'll be on your way to automating the generation of stored procedures.

Atif Aziz

MSDN Magazine August 2002


SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web

  

Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

Dave Grundgeiger, Anson Goldade, and Varon Fugman

MSDN Magazine August 2001


EF 4: Function Import problem with Stored Procedures

  

I have the following SP which includes a temp table:

 DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend