Is it possible to set the result from a prepared statement into a variable? I am trying to create the following stored procedure but it is failing: ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stmt USING @m, @c, @...
I added my local server pblack as linked server in SQL Server 2008 R2 ---1) EXEC master.dbo.sp_addlinkedserver @server = N'pblack', --'pblack' is my localhost @srvproduct=N'SQL Server' and executed successfully 2) and 3): --2) sp_MSforeachtable @command1="EXEC sp_spaceused '?'" --3) SELECT * INTO #te...
Hi, I've a .Net 3.5 windows application.One of the modules uses a Oracle stored proc which returns a recordset to the C# client. I just want to know which one is a better approach among the following two[wrt. code-readability and performance.]: 1.If I write a multiple CASE statement in the stored proc(SP) itself, then the recordset co...
I am doing some tests using the SQL 2005 profiler. I have a stored procedure which simply runs one SQL query. When I run the stored procedure, it takes a long time and performs 800,000 disk reads. When I run the same query separate to the stored procedure, it does 14,000 disk reads. I found that if I run the same query with OPTION(re...
When working with stored procedures in linq, I can go to my SP and select what type of entity it should be returning. This works fine in most cases, except the time's I'm not sure of what will be returned. If I don't select what type of entity to return i get the standard return ISingleResult<SP-Name> I thought I would be able to wor...
Hello guys. Sorry for this maybe foolish question but i'm newbie to SQL Server. Here structure of my table of divisions at organization: id int(16) -- simply unique id sub_id int(16) -- this id of parent division in this table (if zero, than it don't have parent) name VARCHAR(200) -- name of division I need to create a simply procedu...
I need to generate a txt from oracle and put it into a ftp server, I already make the st for build de file but is it possible to transfer it to a ftp directly from the SP??? Thanks. ...
I Have a stored procedure: CREATE OR REPLACE PROCEDURE UpdateFileMapping(field in number, original_Field_Names in DBMS_SQL.varChar2_table, mapped_Field_Ids in DBMS_SQL.number_table) IS C NUMBER := DBMS_SQL.OPEN_CURSOR; N NUMBER; BEGIN DBMS_SQL.PARSE(C,'INSERT INTO input_file_mapping VALUES(input_file_mapping_id.NextVal, 3, field, :fiel...
I am wondering if MySQL's stored procedures can work with two different databases on the same machine? How about if they are on different servers? ...
I have a bit of an odd database model for a project - I'm going to have several tables with the same definition. Each table stores information for different clients and because of security restrictions, we cannot put the data together in one table. So it would look something like this... table ClientOneData ( Id (PK, int, not nul...
I'm trying to use the OUTPUT statement in a stored procedure in order to return the ID of a newly inserted row. The stored procedure is: CREATE PROCEDURE PROC_RESTORE_REQUEST_TO_QUEUE @cs_uri_stem varchar(900), @cs_uri_query varchar(2500), @date datetime, @time datetime, @queue_state smallint, @process_id int, ...
I'm working with SQL Server Reporting Services 2008, which is somewhat new to me, as most of my experience is with LAMP development. In addition, moving most of the logic to SQL as stored procedures is something I'm not very familiar with, but would like to do. Any help or direction would be greatly appreciated. I need a list of accep...
Person | p_id | n_name | l_name | address | city | state | zip | Customer | p_id | reward_points| balance | Person_PhoneNum | ppn_id | p_id | number | Main issue is that I want to attempt making a Retrieve Stored Procedure that can search by any of Person's fields as well as phone number or p_id BUT I want it to be able to handle N...
I'm about to convert a stored procedure from pl/sql to SQL Server. The procedure uses a cursor to loop through the results of a select query. Is there a SQL Server equivalent to the ORACLE rowtype construct? ...
Stored procedures are compiled on first use. There are options to clear cache: DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS --To Verify whether the cache is emptied --DBCC PROCCACHE or to recompile or to reduce recompilations. But is it possible to force frequently used stored procedures' execution plans be pre-cached and stay in...
Simply, I have this SQL statment: EXEC xp_cmdshell 'tasklist' can we order or filter the results by using order by or where? Thanks, ...
protected void Authorise(object sender, EventArgs e) { CheckBox chk; foreach (GridViewRow rowItem in GridView1.Rows) { // gets the Debit Reference number for each row checked string type = GridView1.DataKeys[rowItem.RowIndex].Values[0].ToString(); // gets the Debit E...
Hello...I have a Report in SSRS VS2008 that needs to run a Stord Procedure from Oracle. In the past I have run functions from Oracle that return tables in order to display the data. As well as straight forward SELECT statements For example: select * from table(MyFunction(:parm1, :parm2)) select * from MyTable I have not run a Store...
I have a stored procedure that builds a dynamic where clause and executes the SQL statement. It looks similar to this: declare @customerId int declare @sql varchar(100) set @customerId = 12 set @sql = Replace('select customerName from customer where customerId = @customerId', '@customerId', @customerId) exec @sql I know the above do...
Currently, if we want to get a list of records from the database, our DAL returns a DataTable to our business layer, which then returns the same DataTable to our calling interface (in this case an asp.vb page). However I don't believe we should be returning a DataTable from the BLL, I always thought it would be better to return a strong...