Disclaimer: This is an "asked-and-answered question" posted in accordance with the FAQ statement that it's "perfectly fine to ask and answer your own programming question". Its purpose is to encourage members of the SQL Anywhere programming community to use StackOverflow by seeding the "sqlanywhere" tag with some real-world content. Edit...
Ive Used Subsonic for MSSQL and works wonderfull!!!! Does anyone know whats with oracle's SPS in Packages? Please help. need to deliver a func verry soon. ...
I'm trying to use a procedure (no parameters) to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. Here's what I have so far, but I think I'm going about this the wrong way. create or replace procedure CLEAN_SCHEMA is cursor sc...
Hi. I'm using .NET 2.0 and/or 3.5. Weird thing is, everytime I add a query, whether via TableAdapter or a plain query in a Dataset, using a MySQL stored procedure (whether be a select, update, insert, delete), the wizard doesn't finish (the dialog suddenly disappears, I'm back to designer mode and the query isn't added to the tableadap...
Hi there, can anyone help me with construction of an IF in a stored procedure in sql server. Basically I have a simple stored procedure but I now need to pass in a new input parameter which depending if it is true I pass the value D and if its false I pass the value A. But the change is in the middle of a subquery.. let me explain... h...
I have a stored proc on an existing 3rd party application (SQL 2005) that I wish to interact with. It is an insert statement followed by a select statement as follows; Set @CustomerId = Cast(SCOPE_IDENTITY() As [int]) Select @CustomerId Using VB6 how do I access the value of @CustomerID? set rs = cmd.Execute is not ret...
Hi! I am sorry for the incomplete information. Here's my two sp's: SP1 - DELIMITER $$ DROP PROCEDURE IF EXISTS SP1 $$ CREATE PROCEDURE SP1(InputCustomerID int, InputOrderID int) BEGIN // Some sql statements and finally put it into the actual table tb1 in the database END $$ DELIMITER ; SP2 - In SP2 i am assigning input value of ...
Hi Guys I have the following SP CREATE PROCEDURE GetAllHouses set @webRegionID = 2 set @sortBy = 'case_no' set @sortDirection = 'ASC' AS BEGIN Select tbl_houses.* from tbl_houses where postal in (select zipcode from crm_zipcodes where web_region_id = @webRegionID) ...
I have a Crystal Report, that is populated with a stored procedure, it also contains a sub report, that I am trying to populate with another stored procedure ... same parameters. I cannot figure out how to pass the parameter value to the sub report. Any help would be appreciated! ...
I would like to be able to add a new SQL LOGIN and name it after a person email address. For example "[email protected]". When I pass this to the following stored procedure I get an error (error follows procedure). The stored proc: CREATE PROCEDURE [Forms].[AddLogin] @Email nvarchar(2048), @TenantPassword nvarchar(2048) AS BEG...
I have a table called Person that contain a field called PersonAge. I need to group the ages by age bands ie '12 and under', '13-17', '18-25', '25 and over' and return this resultset using a stored procedure. Ideally I need to get returned 2 fields , 'Age Band', 'Total' like so Age band Total 12 and under 5 13 - 17 ...
If I use stored procedures, can I use an ORM? EDIT: If I can use a ORM, doesn't that defeat part of the database agnosticity reason for using an ORM? In other words, why else would I want to use an ORM, if I am binding myself to a particular database with stored procedures (or is that assumption wrong)? ...
Hi there, I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure. This is working great, but I can't find how to set the schema of this Stored Procedure in phase ...
I have been wondering about temp tables in sp's and how all that can effect concurrency. SP made on a MSSQL 08 server. If I have a SP where I create a temp table and drop it again like this: BEGIN CREATE TABLE #MyTempTable ( someField int, someFieldMore nvarchar(50) ) ... Use of temp table here ... And then.. DROP TABLE #MyTem...
A related question to a previous one regarding the same subject. In a CLR stored procedure, I am bulding a collection of objects which represent a data set after a complex filtering. This collection is then "translated" in a resulset with the SqlContext.Pipe.SendResultsStart / SendResultsRow / SendResultsEnd loop and delivered. Despite ...
I have some Java code that accesses SQL Server 2005 which looks something like this: CallableStatement cstmt = ...; ... // Set input parameters cstmt.registerOutParameter(11, Types.INTEGER); cstmt.execute(); int out = cstmt.getInt(11); And the following exception is thrown from the last line: com.microsoft.sqlserver.jdbc.SQLServerExc...
So, not sure what is happening. But I have stored procedure and it keeps disappearing out of my DB in SQL 2k. I can add it again and then try to execute it from my web app and i get an exception saying the stored procedure cant be found. So then ill go back to management and refresh and its gone again !?! here is the config for the sto...
Attempt to generalize my questions... I want to execute a stored procedure for each result returned by a SELECT statement. Mentally I want to try something like EXEC myStoredProc (SELECT id FROM sometable WHERE cond = @param) More details related to my specific case... I have a SaaS application. I would like to delete a tenant from th...
IN SQL Server 2008 I have a situation where I need to return a dynamically generated table, and the columns are also dynamically generated. Everything in this was generated by queries, as I started with one id, then I get the column names and types, which is why the casting. Following is the final query, that will return the table I wa...
I have some extended classes (utilizing both HMAC and cryptographic tech), and I'm hoping to utilize them for a new product's database from said database's stored procedures (inclusive of time logging, user-event logging, etc.) under SQL Server. Can anyone recommend a resource detailing the best ways to achieve this or optionally advise...