Tag Info

New answers tagged

2

I've never heard of comments causing a problem. The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code. For testing you could add another INSERT to the ...


2

As the error message suggests, OPENQUERY looks for a linked server. If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go. This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string. In your case, you ...


2

RETURN QUERY EXECUTE was introduced with Postgres 8.4. Your version is just too old and unsupported by now. Upgrade to a more recent version. Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front. Returning anonymous records without a column ...


0

With a single stored procedure (SP) you will run into parameter sniffing problems. RECOMPILE becomes mandatory. Every invocation now has a compilation overhead. This can mount up since the SP is, almost by definition, very large. Lots of small SPs are less likely to need OPTION (RECOMILE) individually and the cost of compiling less when it does happen. ...


0

Not there. But your idea is a good one.... In Ada, which was the origin of PL/SQL, you could define a part of a package body, like a procedure, as "separate". This would allow you to break up overly long package bodies (in Ada) without losing the internal variables etc. Definitely would love to see it in PL/SQL.


2

Your question may be a candidate for closing as "Primarily opinion-based" but yes, I'd follow your proposed pattern, to a point. What I tend toward is identifying the most frequent parameter combinations and coding discrete procedures for them. It becomes unfeasible with a large number of combinations of parameters to code and maintain for each, although ...


0

sys.dm_exec_sql_text presents the full text of the procedure including it's CREATE PROCEDURE command. To get the actual query that was executed, you'll need to join to sys.dm_exec_query_stats, where the start/end offset values of the procedure's SQL are stored. SELECT deqs.execution_count, deqs.last_execution_time, SUBSTRING(dest.text, ...


1

I had exactly the same problem! I was trying to report on UCCX and use the output of the stored procedures to populate new tables. I got either Illegal SQL Statement in SPL routine or returns too many values. After some thought I came to the conclusion the only way to achieve this is to re-write the SPLs using JOINS rather than UPDATES. You can then use a ...


0

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of. The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor ...


1

CREATE PROCEDURE `t`(IN `r` TINYINT, OUT `l` TINYINT, OUT `z` TINYINT) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE exit HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN SET l = -1; SET z = -1; ...


0

In the end, we used the SSIS package with a script that captured the print statements as illustrated in the link in my original question. The captured print output is put in to the SSIS log which can then be directed to a CSV file, XML file, database, etc. (we used a database table). We then run the SSIS package using an SQL Server Agent job (just make ...


3

One solution to the rollback problem is to log information to a table variable (which isn't affected by the transaction), then roll back, then insert into the real logging table. Quick example: DECLARE @LogRow TABLE ( dt DATETIME, objectid INT, errornumber INT /* other columns */ ); BEGIN TRANSACTION; BEGIN TRY SELECT 1/0; COMMIT TRANSACTION; ...


2

I know raising the transaction isolation level and adding a transaction can seem counter-intuitive, but sometimes, you have to make things queue up and wait their turn. Doing those two things worked for me in my simple test rig. Try it and let us know how you get on. EDIT: Test rig OK, nice simple test rig using tempdb. It's a bit rough and ready but ...


0

Simply you can use out parameter, this will return the result to it, example: CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT) BEGIN # Set value of OUT parameter SELECT VERSION() INTO ver_param; # Increment value of INOUT parameter SET incr_param = incr_param + 1; END; the calling is going to be like: mysql> SET ...



Top 50 recent answers are included