First of all there is partial question regarding this, but it is not exactly what I'm asking, so, bear with me and go for it. My question is, after looking at what SubSonic does and the excellent videos from Rob Connery I need to ask: Shall we use a tool like this and do Inline queries or shall we do the queries using a call to the stor...
Can't figure out why I'm getting 'SQL Statement ignored' and 'ORA-01775: looping chain of synonyms' on line 52 of this stored procedure. Got any ideas? Thanks, Tony CREATE OR REPLACE PACKAGE PURGE_LOG_BY_EVENT_DAYS AS TYPE dual_cursorType IS REF CURSOR RETURN dual%ROWTYPE; PROCEDURE log_master_by_event_days (event_id NUMBER, purge_r...
I have some complex stored procedures that may return many thousands of rows, and take a long time to complete. Is there any way to find out how many rows are going to be returned before the query executes and fetches the data? This is with Visual Studio 2005, a Winforms application and SQL Server 2005. ...
Some of my MS SQL stored procedures produce messages using the 'print' command. In my Delphi 2007 application, which connects to MS SQL using TADOConnection, how can I view the output of those 'print' commands? Key requirements: 1) I can't run the query more than once; it might be updating things. 2) I need to see the 'print' results e...
I have been searching for recent performance benchmarks that compare L2S and EF and couldnt find any that tested calling stored procedures using the released version of EF. So, I ran some of my own tests and found some interesting results. Do these results look right? Should I be testing it in a different way? One instance of the conte...
Here is a simple scenario with table characters: CharacterName GameTime Gold Live Foo 10 100 3 Foo 20 100 2 Foo 30 95 2 How do I get this output for SELECT Gold, Live FROM characters WHERE name = 'Foo' ORDER BY GameTime: Gold Live 100 3 0 -1 -5 0 using MySQL stored procedure (or query if it's even ...
How do I escape a string in SQL Server's stored procedure so that it is safe to use in LIKE expression. Suppose I have an NVARCHAR variable like so: declare @myString NVARCHAR(100); And I want to use it in a LIKE expression: ... WHERE ... LIKE '%' + @myString + '%'; How do I escape the string (more specifically, characters that ar...
I've written a paged search stored procedure using SQL Server 2005. It takes a number of parameters and the search criteria is moderately complex. Due to the front-end architecture I need to be able to return the number of results that would come back without actually returning the results. The front end would then call the stored proce...
Let's say I have a simple stored procedure that looks like this (note: this is just an example, not a practical procedure): CREATE PROCEDURE incrementCounter AS DECLARE current int SET current = (select CounterColumn from MyTable) + 1 UPDATE MyTable SET CounterColumn = current GO We're assuming I have a table called 'myTable...
I was doing some testing and straight LINQ-to-SQL queries run at least 80% faster than if calling stored procedures via the LINQ query in SQL profiler a generic LINQ query var results = from m in _dataContext.Members select m; took only 19 miliseconds as apposed to a stored procedure var results = from m in _dataContext.GetMembe...
Hello! I'm pivoting data in MS SQL stored procedure. Columns which are pivoted are dynamically created using stored procedure parameter (for exampe: "location1,location2,location3,") so number of columns which will be generated is not known. Output should look like (where locations are taken from stored procedure parameter): OrderTi...
Hi, I have created a proc that grabs all the user tables in a local DB on my machine. I want to be able to create a flat file of all my tables using BCP and SQL. Its a dummy database in SQL 2000 connecting through windows authentication. I have set my enviroment path variable in WinXP SP2. I have created new users to access the db, swi...
I need to write a Stored procedure in SQL server whose data returned will be used to generate a XML file. My XML file to be in structure of <root> <ANode></ANode> <BNode></BNode> <CNode> <C1Node> <C11Node></C11Node> <C12Node></C12Node> </C1Node> <C2Node> <C21Node></C21Node> <C22Node></C22Node> </C2Node> <C3No...
I need to pull some BLOB data from a SQL Server 2005 database and generate a SQL script to insert this same data in another database, in another server. I am only allowed to do this using SQL scripts, I can't use any other utility or write a program in Java or .NET to do it. The other big restriction I have is that I don't have access ...
My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a given point in the script, but is there a better way to debug MySQL stored procedures? ...
I'm working on a Java based project that has a client program which needs to connect to a MySQL database on a remote server. This was implemented is as follows: Use JDBC to write the SQL queries to be executed which are then hosted as a servlet using Apache Tomcat and made accessible via XML-RPC. The client code uses XML-RPC to remotely...
Jeff Atwood wrote about this here, and while I understand the theoretical performance boost a stored procedure could offer, it does seem like a tremendous pain. What types of queries would you see the most performance increase using stored procedures, and what types of queries would you rather just build on the fly? Edit: Any documenta...
We're using Stored Procedures for every query to the DB. This seems incredibly un-DRY: Design the table Design CRUD operation SPs for that table Design code (preferably a class) to fill parameters and execute CRUD SPs If we add a single column, or change a datatype, we have to edit the table, a handful of SPs, and a handful of funct...
I'm writing a simple time tracking program to manage my own projects. I'm a big fan of keeping reporting code in the database, so I've been attempting to create a few sprocs that generate the invoices and timesheets etc. I have a table that contains Clock Actions, IE "Punch In", and "Punch Out". It also contains the user that did this a...
I'm updating a long list of records. In my code, everything run as predicted until it execute the query. It gave me a incorrect syntax near 'TempUpdatePhysicalCityStateZip'. (My store proc name). I've test it on the sql studio and it run fine. So, I'm not quite sure where i got it wrong. Below is my store proc. and code. ALTER PROCEDURE...