I need to perform check table, optimize table and analyze table statements on all tables in a database. And I want to do it inside a stored procedure. Is it possible? I can't figure out how to iterate through the table names and then use those names to perform the 3 statements. ...
Correct me if I am wrong, but this SQL command: create table MYTABLE (ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)) does not need the NOT NULL part, as a primary key is suppose, by default, to be not null. Isn't this reduntant? (I'm not secure to just test it and agree in the result, pr...
Currently there is discussion as to what are the pros and cons of having a single sql connection architecture. To elaborate what we are discussing is, at application creation open a sql connection and at application close or error closing the sql connection. And not creating another connection at all, but using just that one to talk wi...
why this query give me an error:ORA-01790 SELECT TO_CHAR(logical_date,'MM') MONTH FROM logical_date WHERE logical_date_type='B' UNION SELECT TO_CHAR(logical_date,'MM')+1 MONTH FROM logical_date WHERE logical_date_type='B' but when i run them separately,they give the proper output. ...
In SSMS I've gotten my for xml path query written and it's beautiful. I put it in an "Execute SQL Task" in the Control Flow, set the resultset to XML. Now how to I get the results into an actual xml file that I can turn around and FTP to a third party? This should have been so easy! I would put the XML into a variable but we are looki...
i tried something like this but the id value is not changing, getting the value for first record and set the value for all rest... insert into table1 (Id,b,c,d) (select (select max(Id)+1 from table1),x,y,z from table2 where... ) ...
I have a .Net/c# 2.0 windows service. The entry point is wrapped in a try catch block that notifies me of problems and allows the service to continue operating normally yet when I look at the server's application event log I see a number of "EventType clr20r3" errors that are causing the service to die unexpectedly. The catch block has...
declare @t1 Table ( a1 int ) insert into @t1 select top 10 AnimalID from Animal --select * from @t1 declare @t2 table ( dogs int null ) update @t2 set dogs = (Select COUNT(*) from @t1) ---------> The out put it gives me is just 0 ...
I've got a fully custom PHP site with a lot of database calls. I just got injection hacked. This little chunk of code below showed up in dozens of my PHP pages. <?php /**/ eval(base64_decode(big string of code.... I've been pretty careful about my SQL calls and such; they're all in this format: $query = sprintf("UPDATE Sales SET `S...
I've got a stored procedure with a select statement, like this: SELECT author_ID, author_name, author_bio FROM Authors WHERE author_ID in (SELECT author_ID from Books) This limits results to authors who have book records. This is the Books table: Books: book_ID INT, author_ID INT, book_title NVARCHAR, featured_book B...
Based on an existing table I used CTE recursive query to come up with following data. But failing to apply it a level further. Data is as below id name parentid -------------------------- 1 project 0 2 structure 1 3 path_1 2 4 path_2 2 5 path_3 2 6 path_4 3 7 path_5 4 8 path_6 ...
I have a database with the following tables: Employee (EmpID, FirstName, LastName, RegionID) EmployeeSkills(EmpID, SkillID) [this is a linking table for the M:N relationship between Employees and skills] Skills(SkillID, Description) I need to list the name of the skill that most employees have. I tried doing a max(count(skillID)), sql...
One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table? ...
I have a few tables where Foreign Key constraints are added. These are used with code generation to set up specific joins in generated stored procedures. Is it possible to override these constraints by calling multiple deletes within a transaction, specifically "TransactionScope" in C# or is cascaded deleting absolutely required? ...
I have two databases (SQL Server 2005) with the same table schemes. I need to copy data from source table to destination with some modification of data along the way. And if destination table already contains some data, then rows from source table should not override, but be added to the destination table. In our project we use LLBLGen...
Hi, I need to know a way to show a message or something like that when I compile my program and the user or password are wrong, because when I do this nothing happens. I'm making a system that save logs. log4net just using C#. if you can help me I'd appreciate. thanks! ...
Imagine that we have two tables as follows: Trades ( TradeRef INT NOT NULL, TradeStatus INT NOT NULL, Broker INT NOT NULL, Country VARCHAR(3) NOT NULL ) CTMBroker ( Broker INT NOT NULL, Country VARCHAR(3) NULL ) (These have been simplified for the purpose of this example). Now, if we wish to join these two tables on the...
Hi guys! Im sure this will be pretty simple for a t-sql guru. I have the following result from a table idMain IdSecondary TextValue 1 1 text1 1 2 text2 2 5 text3 2 6 text5 And I want to obtain the first occurence of the idMain only. The result should be like this....
Thought I would ask the knowledgeable StackOverflow community a question that was on my mind today and I can't seem to find an answer. Is there any performance difference or advantage between an "IN" or using "OR"? Ie. Is SELECT type FROM types WHERE typecat IN ('abc', 'def') better than SELECT type FROM types WHERE typecat = 'abc' ...
I'm working with an oracle DB trying to tune some queries and I'm having trouble understanding why working a particular clause in a particular way has such a drastic impact on the query performance. Here is a performant version of the query I'm doing select * from ( select a.*, rownum rn from ( select * fro...