Tag Info

New answers tagged

0

Create a linked server connection with read access from Server B to Server A. Have a process running on Server B that does the following: Examine the table on ServerB to identity the last inserted record from Server A. Hopefully you have an auto-incrementing primary key (int). If not, look for the greatest "last updated timestamp". Connect to Server A ...


0

I have create following Procedure which goes thru all SP and Views in particular DB (can be extend to Functions, ...) and store each code script one by one into TXT files. Tested ON MS SQL 2008 R2 and 2014 First part Inserting all scripts of SP and Views into Temp table. Then later using BCP utility. If you wish you can can use for export SSIS package and ...


3

You say that your procedure has two parameters: an input INTEGER and an output VARCHAR, however, you call a procedure with only one argument, therefore DB2 is looking for the procedure that has only one INTEGER argument. You should call it like so: call ADMINIST.LOG_MSG_TEST(1,?); where the question mark is a placeholder for the output argument.


0

It depends on your Powershell versions but you have a few options. I tend to use several of them based on the scenario but in your case, why not just call a SQLCMD.EXE from your powershell Script? It has the most compatibility, it's less buggy and more compatible than Invoke-SQLCMD, and outside of DSC I haven't seen MS use osql. It would look something ...


0

Create dummy, empty tables on the local server so that the procedure creation can happen. Change the procedure code to use two-part synonyms. On the server that needs to use the linked server: CREATE SYNONYM dbo.whatever FOR linkedserver.dbo.whatever; On the local server that doesn't need the remote references to exist: CREATE SYNONYM dbo.whatever FOR ...


0

Make sure the event scheduler is on SET GLOBAL event_scheduler = ON; and then create the event as below: CREATE EVENT hourly_temp_avg ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO `table1` (`dateavg`, `avg_temperature`) SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:00:00') , avg(`temperature`) FROM `new_table` WHERE `date` ...


0

If this is SQL Server procedure, leave out the parenthesis and commas: Create Procedure sp_PersonEditor @BusinessEntityId int not null, @PersonType nchar(2) not null, @NameStyle bit not null, @Title nvarchar(8) null, @FirstName nvarchar (50) not null, @MiddleName nvarchar (50) null, @LastName nvarchar (50) not null, @Suffix nvarchar (10) ...


2

LOG_CONF_BP is a bufferpool. That is a chunk of memory dedicated to doing something (usually servicing one or more tablespaces, which is where tables reside). So what is happening is your stored procedure is trying to create an object that already exists. So what is going to need to change is the stored procedure. I would ask if your stored procedure needs ...


1

You can use prepare statement for this and also need a delimiter for the procedure something as delimiter // create procedure table_create (in tablename varchar(100)) begin set @table_ = concat(tablename, '_meta'); set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;"); prepare stmt from @qry; execute stmt; ...


2

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as DELIMITER $$ USE `smsdev`$$ DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$ CREATE /*!50017 DEFINER = 'smsdev'@'%' */ TRIGGER `pageprivilege_update` ...


1

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server. Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is: bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T You would then attach the file ...


1

Yes you can send the report via HTML format, for example as listed in MS: Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail ...


1

To my knowledge, there are no tools like doxygen that can make doc from parsing SQL annotated code. About keeping comments, note that comments in a stored procedure can be saved with the procedure code. When a CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the mysql client is using the -comments option. See ...


0

Eventually I found a starting point thanks to Verace. But comments can be long, therefore i post this as an answer: DROP PROCEDURE IF EXISTS foobar; DELIMITER // CREATE PROCEDURE foobar() COMMENT ' This procedure does some useful stuff ' BEGIN -- Do something useful here SELECT 1; END // DELIMITER ; SELECT ROUTINE_COMMENT FROM ...


1

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA. Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'yourdb' ...


0

In your stored proc declare parameters like below Create procedure addFaculty ( @ID int OUTPUT ) as begin Insert into CredentialsTable values ('iamafaculty'); Select @ID = CredentialsTable.CredentialsID from CredentialsTable where CredentialsTable.Password = 'iamafaculty'; end Then finally while calling,retrieve the id value ...


1

If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query: Create procedure addFaculty @ID int OUTPUT as begin IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty') begin Insert into CredentialsTable values ('iamafaculty'); Select ...


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...


1

Credits to @sabin bio for this Using the Scope_Identity() returns the last identity in any table in the current session. The scope of the Scope_Identity() is limited only to the current scope which is where the last insert statement has been made. https://msdn.microsoft.com/en-us/library/ms190315.aspx


0

What you're describing sounds like parameter sniffing to me. Parameter sniffing occurs because SQL Server caches the execution plan of the procedure if the plan is not found in the cache. Subsequent executions will use that version of the plan. In most cases, that's desirable since it eliminates compilation time. However, if the characteristics of the data ...


3

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan. If you are ...


2

Is it possible to execute stored procedure in another stored procedure in oracle Yes. Demo Let's say I have two procedures, P1, and P2, such that Procedure P1 takes an input as employee number, and gives the employee name as output. Procedure P2 will be called inside P1 which will take the output of procedure P1 as input, and give it as output. ...


1

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...



Top 50 recent answers are included