stored-procedures

ssis variable syntax conflicts with mysql output variable

I've created an SSIS package that needs to execute a MySQL SPROC with an output parameter. The MySQL SPROC works fine from Query Browser. The problem is that the @ character is used to mark a SSIS variable and also is used as a MySQL sproc variable for output. The step does not work in SSIS. Does anyone know a fix? ...

Arguments for/against Business Logic in stored procedures

What are the arguments for and against business logic in stored procedures? ...

SSIS return value of Stored Procedure within an OLE DB Command

I am migrating data that has to be inserted using stored procedures which already exist. The stored procedures have parameters and a return value (from a select statement) of an id for the row inserted. Within an OLE DB Command in SSIS I can call the stored procedure passing column values as the parameters and I usually use output paramt...

Best way to delete all rows in a table using NHibernate?

To keep my integration tests independent I remove all old data and insert new test data before each test. Is there a better way of doing this than simply querying for all entities and deleting them one by one? I have considered writing a stored proc that runs "delete from tablename;" for each table that is to be cleared. That ought to q...

Best way to create a default Database setup via an .aspx page?

We are going to be selling a service that will be hosted by us, and each client we host will have their own database, but there will be one centralized website. I currently have a blank database with the few things that a new client will need. What is the best way to copy this database so I can setup another client? I want to be able ...

Most secure Flash > MySQL connection

Currently the major means of connecting Flash AS2/AS3 to a MySQL database are: Flash > PHP > MySQL -- "secure code" in PHP Scripts Flash Asql or Assql > MySQL -- "secure code" in MySQL Stored Procedures The second approach is newer, but directly connects to a MySQL DB using binary sockets and ByteArrays. In which case would the "sec...

How do I get Linq to SQL to recognize the result set of a dynamic Stored Procedure?

I'm using Linq-to-SQL with a SQL Server backend (of course) as an ORM for a project. I need to get the result set from a stored procedure that returns from a dynamically-created table. Here's what the proc looks like: CREATE procedure [RetailAdmin].[TitleSearch] ( @isbn varchar(50), @author varchar(50), @title varchar(50)) as declare ...

Stored procedure Delete query problem

I have stored procedure: ALTER PROCEDURE [dbo].[k_ShoppingCart_DELETE] @cartGUID nvarchar AS DELETE FROM [dbo].[k_ShoppingCart] WHERE CartGUID = @cartGUID When I execute this sp; exec dbo.k_ShoppingCart_DELETE '32390b5b-a35a-4e32-8393-67d5629192f0' result: 0 row (s) affected Actually when I try this query: Delete FROM k_S...

When I should use stored procedures?

Is it always good to use stored procedures , is it good for database performance and what kind of queries that you better use stored procedures? Exact Duplicate: When should you use stored procedures? ...

T-Sql How to return a table from a storedproc in another stored proc.

I would like to do the following. Basically have a stored procedure call another stored procedure that returns a table. How is this done? ALTER PROC [GETSomeStuff] AS BEGIN @table = exec CB_GetLedgerView @accountId, @fromDate, @toDate, @pageSize, @pageNumber, @filter, @status, @sortExpression, @sortOrder, @virtualC...

Problem importing mysql procedures generated from mysqldump --routines

I use the mysqldump tool to make copies of my database. The problem is, when I use the --routines parameter to output my stored procedures along with my data, the generated output causes an error when I try to import it. It goes something like this: % mysqldump --routines MyDB | mysql MyDB2 (where MyDB2 already exists but is empty) ...

SQL - Query Cubes

So i just received a connection string to a Cube in our one of our SQL databases. I'm supposed to create a report that can be emailed to 30 something people off of this data. I would much rather write a stored procedure and just let SSRS send it out whenever its supposed to. The problem is that the database is extremely complex and I'm ...

Different Execution Plan for the same Stored Procedure

We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame ...

Are stored procedures required for large data sets?

I've just started my first development job for a reasonably sized company that has to manage a lot of data. An average database is 6gb (from what I've seen so far). One of the jobs is reporting. How it's done currently is - Data is replicated and transferred onto a data warehouse. From there, all the data required for a particular repo...

Are SQL stored procedures case sensitive?

for example... ALTER PROCEDURE [dbo].[Reports_Dashboard_Get_Sav] -- Add the parameters for the stored procedure here @startDate datetime, @endDate datetime, @companyID int=null set @days=datediff(m,@startdate,@enddate) if (@days)=0 set @days=1 This is not my code but if this is case sensitive then @days is not...

Access Concatenate rows into single rows: extra conditions needed

I'm working with an Access database and I need to concatenate different related rows into 1 row. I found a solution here and used it with great success. However I need to add extra conditions to it, it should only be done if certain other columns are equal too. For example: 1 X Alpha 2 Y Beta 1 X Gamma 1 Z Delta should become 1 X A...

MSSQL: How do you script Stored Procedure creation with code?

I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another. SELECT t1.Routine_Definition FROM [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 LEFT JOIN [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name WHE...

SQL Server: Is it faster to retrieve or filter data from views using stored procedure than getting or filtering data from tables using stored procedure?

Hi, are there performance issues when retrieving and filtering data from views than from tables by using a stored procedure? Simplification: Is it faster to retrieve or filter data from views using stored procedure than getting or filtering data from tables using stored procedure? ...

MS SQL stored procedure returned result sets with ODBC

I have a stored procedure and if the stored procedure does this: SELECT 0 As Ret DELETE FROM table where value1 = 1 Returns 1 row result with its value of 0 and column name Ret But if I do this: DELETE FROM table where value1 = 1 SELECT 0 As Ret I get no returned results. My question is, how do I get the second variation to retu...

Stored procedures are timing out intermittently!

I have a number of stored procedures I call from code with ExecuteNonQuery. It was all good but 2 of my stored procedures started timing out intermittently today with: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. If I e...