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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Create Scripts creates invalid default values for CLR based Stored Procedures

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

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!

View Complete Post

More Related Resource Links

Replication stored procedures not being passed parameters- default appears instead

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'

A Stored Procedure to Create a Set of Stored Procedures


Is it possible to use a stored procedure to create a bunch of other stored procedures?  When the user creates a new database, I need to create a bunch of stored procedures and I would like to just run a stored procedure on the new database that creates all the needed stored procedures.  I tried just putting the CREATE PROCEDURE syntax inside a stored procedure and got some errors.  I imagine this can be done using dynamic sql and the EXECUTE function but I was hoping there was a better way.



Nick's Programming Tips

Puzzled about stored procedures Create



If a stored procedure is a stored procedure, then why when I am creating a stored procedure in VS 2010 for a windows forms program, do I have to include a 'Create procedurename' in my stored procedure?

Doesn’t the add stored procedure function in VS 2010 already know that it is a stored procedure already?

I'm not trying to be funny about it.  It just seems like some sort of redundancy.  Maybe somebody's answer will give me a better idea of what SQL Server is and what a stored procedure is.

Any help would be gratefully appreciated.

Stored Procedures Only Return integer values?


I have the following stored procedure

ALTER PROCEDURE dbo.GetProvMoveModifier
	@prov_id int 
	DECLARE	   @mod float
	WHERE      (PROV_DETAILS.PROV_ID = @prov_id)
	RETURN     @mod

MOVE_MOD is defined as a float.

Yet within Dataclasses.designer.cs the function gets added as:

	public int GetProvMoveModifier([Parameter(DbType="Int")] System.Nullable<int> prov_id)
		IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), prov_id);
		return ((int)(result.ReturnValue));

Naturally this is causing some issues as my C# code needs to handle decimal values.  If I try to manually change the designer code to use floats, I get the following error

'System.Single' is not a valid return type for a mapped stored procedure method.

Any thoughts?  I could setup a work-around, but I would think I could return a decimal value from a stored procedure.

Create a task list from existing task lists based on field values


Hi!  I have a bunch (~10) of different task lists corresponding to different projects; each list has a mixture of Statuses, i.e., some completed, some In Progress, some Not Started.  I'd like to create a (preferably dynamically updated) single task list consisting of all the incomplete tasks from each of the project lists.  After much trial and error (I'm still a relative newbie with DB stuff; even more so w/ Sharepoint stuff) I figured out how to do this by viewing the lists in Access and creating a query which resulted in the compiled list I describe above; however, when I clicked on the "Publish" to Sharepoint button and returned to Sharepoint default view, I couldn't find this new list anywhere (e.g., the new, empty list I had created and changed to Access view, and from which I imported the other lists and created my query, was empty, and my list of task lists in the left side-bar didn't have my query-result-list).

How do I do what I want, w/ or w/out Access?  Thanks!


What would be the best & easy way to create XML using Stored Procedures


What would be the best & easy way to create XML using Stored Procedures. We use SQL 2008

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

how to bind Dropdownlist based on textbox values



I have one drodownlist which show data based om textbox values...

Let us consider i have 

Select Name from table1 where Type IN(@typ)

here @typ is textbox values

here for example textbox will have some thing like this L1,L2,L3..

How to filter Name based and shown in drodownlist based on this L1,L2,L3(on textbox)..

Help me in this

SqlDataSource how to change the stored procedures at run time



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?



Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



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

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