stored-procedures

Pymssql: Calling Stored Procedure with parameters

I am using pymssql to make database calls to a SQL 2005 database. I want to pass parameters to a stored procedure. I am having a hard time getting the correct syntax for that. Here is what I have for calling a procedure with no parameters: import _mssql connection = _mssql.connect(server='myserver', database='mydatabase', trusted=True) ...

Passing variables to stored procedure with Classic ASP

Ok so I am trying to pass some string variables from a classic ASP page to an MSSQL2000 db thusly: strSQL = "exec UpdateEvent " & xID & ",'" & xEventID & "'," & xEventDisplayName & "," & xEventType & "," & xEventStatus & "," & xStartDate & "," & xEndDate & "," & xSurveyTemplateID & "" Yet I end up with the error (including writing out...

MySQL stored procedure causing problems?

EDIT: I've narrowed my mysql wait timeout down to this line: IF @resultsFound > 0 THEN INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; Any idea why this would cause a problem? I can't work it out! Hi guys, I've written a stored proc to search for products in cer...

Select row above and row(s) between next matching row or last row

...

to separate comma separated values of field and add them in integer field of temp table

I have one table member_details with field "preferred_location" (varchar) that has comma separated values like "19,20,22" that come from a listbox selection .... Now I also have another table city_master having field "city_id" (int) and "city_name" (varchar)... Now I want to separate "preferred_location" (varchar) values and to add the...

are stored procedures faster than functions or not

I am using stored procedures at the moment but I have a lot of stuff like this: if ... begin select 'success' return end if ... begin select 'notgood' return end if ... begin select 'someelse' return end so I'm thinking that it would be a better idea to use functions instead, but is there any performance difference between functions a...

Timeout in SQL Procedure

I am using the below sql to import some data from a file from the intranet. However every once a while, there will be a timeout error and the proc would fail, which is why I am using a transaction. If the transaction fails, I want the ImportedTable to get cleared. However this does not seem to happen. Is there anything I am missing here?...

Keeping db structure and procs in sync

I am trying to setup a test database for automated tests to run against. I know ideally we should be using mock objects, but this is an old app and doing so isn't as easy as it should be. There's simply too much logic in the database. Anyhow, enough backstory. I'm getting a clean db setup that the tests can load with known data so th...

MySQL stored procedure, handling multiple cursors and query results

How can I use two cursors in the same routine? If I remove the second cursor declaration and fetch loop everthing works fine. The routine is used for adding a friend in my webapp. It takes the id of the current user and the email of the friend we want to add as a friend, then it checks if the email has a corresponding user id and if no f...

create/ alter from SQL stored procedure

hi I want to call create table/ alter table command from a procedure. Is it possible? My requirement is to change the datatype of a column in all tables. So, I am just getting the column name from user_tab_cols. Now I want to create a temp table which requires create statement .. but i am unable to use that within a proc. Can anyon...

Entity Framework - Mapping doesn't map for stored procedure?

I have a stored procedure that search a view using full text. I'm trying to map the results to an existing Entity (Sales), where I mapped the column MivType to SaleType (as it makes more sense, and I want to keep db names away from my web site). The stored procedure is mapped to a Function Import, and I've defined its ReturnType to Sales...

SQL: avoiding hard-coding or magic numbers

Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures? Consider a stored procedure whose job is to check/update a value of a record based on its StatusID or some other FK lookup table or range of values. Consider a Status table where the ID is most important, as...

What database user permissions are needed?

FYI: SQL Server 2005 I have a database user account (user_web) that has the ability to connect to and run queries and stored procedures in my database. Specifically, I have given the user the db_datareader and db_datawriter roles as well as granted them execute permission on the specific stored procedures it needs to be able to run. In...

Linq to SQL (designer) does not understand stored procedure

Why can the Linq to SQL designer not understand the following stored procedure and import it correctly? CREATE PROCEDURE dbo.MartinTest @parameter1 INTEGER AS SET NOCOUNT ON SELECT C1, C2, C3 INTO #myTempTable FROM MyTable SELECT C1, C2, C3 FROM MyOtherTable INNER JOIN #myTempTable ON .... RETURN W...

Getting the Rows Updated Count using stored procedures and Linq to SQL

I have a Linq to SQL data context class, where I am firing a stored procedure. I can trace through the generated code to where the stored procedure is being fired. I inspect System.Data.Linq.SqlClient.SqlProvider.ExecuteResult and can see that there is a private member called "value" which contains the number of rows updated. There do...

Export an Oracle database, import it to a different user, stored procedures don't work

I export an oracle "schema" using exp userid=/ file=pt.dmp log=pt.log owner=FOO buffer=10000000 statistics=NONE direct=Y and then import it into a different schema on the same oracle instance on the same SID using imp userid=/ file=pt.dmp fromuser=FOO touser=paul When I try to access the stored procedures with the new user, I get...

Is there a way to have SQL server validate object references in Stored Procs?

The following code executes fine in SQL Server create proc IamBrokenAndDontKnowIt as select * from tablewhichdoesnotexist Of course if I try to run it, it fails with Invalid object name 'tablewhichdoesnotexist'. Is there any way to compile or verify that Stored Proc are valid? ...

SQL Server catch error from extended stored procedure

Hello I have an extended stored procedure that sends an error message. srv_sendmsg(pSrvProc, SRV_MSG_ERROR, errorNum, SRV_FATAL_SERVER, 1, NULL, 0, (DBUSMALLINT) __LINE__, buff, SRV_NULLTERM); I've set the severity to SVR_FATAL_SERVER just as a test to see if I can cause the message to throw an exc...

Has anyone suffered any performance issues with CodeSmith or MyGeneration

I am new to both these pieces of software. A colleague of mine recommended these two to me. Just wanted to know if the stored procedure code they produce have any performance problems? I would like to use the software to produce stored procedures only. Anything on these two I should know about? ...

approaches to Build servers and stored procedures

Hi all, I have been put in charge of looking at setting up a build server for our office. We currently put all queries into stored procedures in SQL 2000 server. This is done manually and no SQL files are produced or put into SVN. What I am after is a good way of dealing with having a build server that can get all the stored procs fr...