New answers tagged

1

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command? DECLARE @RunStoredProcSQL VARCHAR(1000); SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]'; --SELECT @RunStoredProcSQL --Debug EXEC (@RunStoredProcSQL) AT [LinkedServerName]; Print 'Procedure Executed'; That's what I have used successfully in the ...


0

Sounds like a job for SSIS. Set up an SSIS package, and use the Execute SQL Task to execute your stored procedures. You can then execute the SSIS packages through Agent jobs.


2

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects ...


0

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)? I would also check various wait statistics, using Paul Randal's query as good starting point http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


0

If this were me, I'd import the data into staging tables in SQL, then run comparisons and update what's needed. As far as physically connecting to Oracle, you should be able to use the OLE Provider for Oracle in SSIS.


2

Thanks for the suggestion, but I want to replace rows -- not update field by field in every modified row. So after some research I think that the best thing I would have to do is to first delete all the old rows that will be replaced with new data. DELETE FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableB.refno = TableA....


3

merge production as target using raw as source on target.refno = source.refno when matched and souce.date > target.date then update set target.date = souce.date, target.value= souce.value when not matched then insert ... merge


3

This example code should work, but there are some things to watch out for. Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue. For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/...


1

(emphasis added) Is there any way to definitively prove it was/wasn't No, there is no way to prove in any definite way that it has been executed, outside of coding the stored procedure to do something like insert a record into a log table showing that it had been executed. In your particular case, this would also require a time machine in order to put ...


2

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status on when was the stored procedure last executed select db_name(database_id) as dbname,last_execution_time from sys.dm_exec_procedure_stats


0

use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(128) ) BEGIN IF (TRIM(_licenseName) = '') THEN select * from SystemLicensing; ELSE select * from SystemLicensing where LicenseName=_licenseName; END IF; END $$ DELIMITER ;


3

You're just missing the keyword THEN. Read more about the correct syntax here. Try it like this: use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(128) ) BEGIN IF (TRIM(_licenseName) = '') THEN select * from SystemLicensing; ELSE select * from SystemLicensing where LicenseName=_licenseName; END IF; END $...


0

I used the MySQL set theory operator UNION ALL to make the MySql stored procedure compile. use puppy; DELIMITER // CREATE PROCEDURE spGetSystemLicensing ( IN arg char(128) ) BEGIN SELECT * from SystemLicensing WHERE TRIM(arg) = ' ' UNION ALL SELECT * from SystemLicensing where LicenseName = arg; END // Please leave a comment if you would ...


0

Strictly speaking, there are not true "stored procedures" in Postgres. Just functions - doing almost but not quite the same. The best option I see is putting RAISE LOG statements into plpgsql function bodies. One other option that comes to mind: the additional module auto_explain. It's actually used to log all query plans and generates huge amounts of log ...


2

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions. There is no facility to write all function calls to the log. That would be quite bulky. If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called. ...


1

Don't use FIND_IN_SET ( SELECT GROUP_CONCAT ... ). Instead JOIN the two tables together.


2

Step 1: Get the SQL script when the function / procedure was created SHOW CREATE FUNCTION my_schema_1.my_function; We will get an result looks like CREATE DEFINER=`mysqladmin`@`%` FUNCTION `my_function` (i_paramter1 char(255), i_paramter2 int) RETURNS char(3) CHARSET utf8 COLLATE utf8_bin READS SQL DATA BEGIN DECLARE result_value char(3); ...


0

Change tmpdir; see http://dev.mysql.com/doc/refman/5.7/en/temporary-files.html You should move from MyISAM to InnoDB. You can probably do the entire stored procedure in a single "multi-table UPDATE" statement. (Cursors are not efficient.) But, perhaps most importantly, switch from FROM a,b to FROM a JOIN b ON ... at which point you will realize that you ...


0

Thank you everyone for suggestions in comments. I've found out two solutions. Use SQL stored procedure instead of PL/pgSQL: CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure() RETURNS VOID AS $test_create_rule_from_stored_procedure$ CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x DO INSTEAD INSERT INTO ...


0

OK, based on feedback from Erwin Brandstetter above, I came up with a VERY nice solution detailed below: CREATE TABLE device_data ( collected TIMESTAMPTZ, id VARCHAR(100), data JSON, PRIMARY KEY(id,collected) ); COPY device_data (collected, id, data) FROM stdin; 2016-01-02 00:02:12+00 switch1.mycompany.com {} 2016-01-02 00:02:12+00 switch2....


1

Not quite what you asked for, but this query may help: mysql> SELECT db, name FROM mysql.proc WHERE body LIKE '%temporary%'; +---------------+-----------------------------------+ | db | name | +---------------+-----------------------------------+ | common_schema | rdebug_compile_routine | | common_schema ...


1

You cannot use @variable_name to declare variable. @ can be used only for session variables. Change your code to this, it will work DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE batchsize BIGINT DEFAULT 1; END //


2

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable. Change the name of your variable from @batchsize to batchsize, and then read up on MySQL ...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


1

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic. You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're ...



Top 50 recent answers are included