Hi I am having fun trying to get a stored procedure to parse correctly in MySQL. My issue is with dates. I am trying to get the store procedure to create a start date that is the beginning of the current month e.g. 2009-07-01. Using this date I then use the DATA_ADD() function to add a month so that it reads 2009-08-01. However my pro...
Hi, I have a TableAdapter that is fetching rows from an Stored Procedure. For some reason, when there are no results, the TableAdapter returns an INT with value 0 instead of NULL. The SP has NOCOUNT ON. The problem with this is that I have a ListView in the frontend with an EmptyDataTemplate, which is not being shown. When I ran the qu...
Hi I have the following scenario. I am calling stored procedures to MySQL 5.x from my .NET 2 application. All stored procedures have parameters and they work fine with the exception of two which throw the following exception (only when deployed to production): "When calling stored procedure and 'Use Procedure bodies' is false, all param...
I have created numbers of stored procedures in MySQL through remote access. And those procedures worked well. After a few days when I connected to Database through remote access. All stored procedures were alive but while was trying to call those procedures, I realized that SQL queries inside procedures had gone. Anyone knows the reason...
Hi Friends, I've created SQL stored procedures in our development schema and its working fine. Now we are in the task of moving our development codes to production one. How do I copy those stored procedures from one schema to another schema? Do I need to re-compile whole procedures on targeted schema? Or Is there any way register those p...
Hello Is there anyway I can execute stored procedure via EXEC (so not specifying command type as StoredProcedure in C#) and get result? I tried to execute query DECLARE @R int EXEC @R = proc p1, p2 and then to grab @R but without success. What I don't want is to do it the usual way by adding parameters and using ParameterDirection...
My company's polices forces to use only stored procedures (SP) to talk with DB (no dynamic execution of SELECT, VIEWS on app side available) Business objects (BO) are fetched from DB. There is implemented simple BO-DB mapper using SQL Reader and this seems working well. Form DB side we have one parametrized SPs for each Business Object ...
I have a stored procedure that returns a scalar int. I am using C# and Linq to get the resulting data, but I get the following error when I compile: foreach statement cannot operate on variables of type 'int' because 'int' does not contain a public definition for 'GetEnumerator' My question is how do I get the resulting data if the st...
I'm trying to accomplish what is described here: http://sqldev.wordpress.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/ The article says (with support via comments) that this works on SQL sever 2000 but may not be as easy. This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be do...
I am developing a report in Sql Server Reporting Services 2005, connecting to an Oracle 11g database. As you post replies perhaps it will help to know that I'm skilled in MSSQL Server and inexperienced in Oracle. I have multiple nested subreports and need to use summary data in outer reports and the same data but in detail in the inner ...
Hello, I heard it's not good to use cursors, as they "unnatural" to DBMS and they provide bad perfomance. But imagine the following situation: I have a stored procedure and I need to call this stored procedure for every customer from France (for example). I have a few options, such as using cursor, write all stuff in one query and call s...
I have a stored procedure that logs some data, how can I call this with NHibernate? So far I have: ISession session = .... IQuery query = session.CreateQuery("exec LogData @Time=:time @Data=:data"); query.SetDateTime("time", time); query.SetString("data", data); query.?????; What should the method ????? be? Or am doing something more...
An application keeps track of hundreds of variables. Users are able to create conditions which can be simple (ex: if one variable is true) or complex (ex: if 34 variables are true AND 22 variables are false AND 2 variables are equal to each other). If the condition ever becomes true, it fires off some action. What is the best way to achi...
I'm writing a fairly complex stored procedure to search an image library. I was going to use a view and write dynamic sql to query the view, but I need to use a full text index, and my view needs outer joins (http://stackoverflow.com/questions/1094695/ms-sql-2005-full-text-index-on-a-view-with-outer-joins) So, I'm back to a stored proc...
Hi, SO crowd! Is there any way to retrieve the names of the stored procedures by some SELECT from the system table or any system SP? I need to retrieve all the stored procedure names with their signatures (if possible) or just names. I know how to do that in MySql, but similar queries don't work (of course, since all the system DBs na...
Hi I'm using VWD 2008 (SP1) and SQLExpress 2008. I'm creating a database first and then looking at using DynamicDataControls (seem useful and staright foward for a beginner like me). Within VWD I created a new Dynamic Data Web Application. Then I added a new SQL database item. I noticed that this doesn't include stored procedures or......
I need to rename all of my tables, stored procedures and obviously the code within each stored procedure that was referencing the old table names. Why is the best way to do this? Some methods I have considered: SP_Rename - Gets half the job done. However this doesn't change the code within the SP itself In addition to RedGates' Refa...
I'm trying to map the output of a stored procedure to an object in my project using nHibernate. The object is delcared like this: public class NewContentSearchResult { public string Name { get; set; } public string ContentType { get; set; } public int Count { get; set; } public int CMIId { get; set; } public int Fea...
LINQToSQL doesn't like my sproc. It says that the sproc has return type "none", which is probably, because I am using an sp_ExecuteSQL statement to produce the results. The SQL Server Sproc Code I have a stored procedure similar to the following CREATE PROCEDURE Foo @BarName varchar(50) AS BEGIN DECLARE @SQL NVARCHAR(1024) SET @S...
Hi , How can we find a particular stored procedure. I need to find a stored procedure which I don't know is in which database. Can somebody please, help with a script. Thanks ...