sql

Creating Stored Procedure Syntax, relating to use of GO

Does anyone know why. CREATE PROCEDURE My_Procedure (@Company varchar(50)) AS SELECT PRD_DATE FROM WM_PROPERTY_DATES WITH (NOLOCK) WHERE PRD_COMPANY = @Company GO Gives me an error message in SQL management studio: Msg 102, Level 15, State 1, Procedure My_Procedure, Line 1 Incorrect syntax near 'GO'. ...

Deleting duplicate rows from a table

I have a table in my database which has duplicate records that I want to delete. I don't want to create a new table with distinct entries for this. What I want is to delete duplicate entries from the existing table without the creation of any new table. Is there any way to do this? id action L1_name L1_data L2_name...

How to created a "singleton" scheduled job in oracle?

It's probably just the vocabulary I am missing to find out how to do this: A job scheduled to run regularly every 5 mins, however keeping track that there are never two instances of it running at the same time, i.e. the next instance would be postponed or skipped if the prior runs longs than 5 mins. What is the easiest/most elegant way...

How to add data to two tables linked via a foreign key?

If I were to have 2 tables, call them TableA and TableB. TableB contains a foreign key which refers to TableA. I now need to add data to both TableA and TableB for a given scenario. To do this I first have to insert data in TableA then find and retrieve TableA's last inserted primary key and use it as the foreign key value in TableB. I t...

Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

Hi, I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, Postgre at least) could also be interesting. I've been doing some "CREATE TABLE" and "CREATE VIEW" for the created table inside a transaction and there seems ...

how to write select statement like this?

Hello everyone, I am using SQL Server 2008. I have a table which is composed of three columns, ID as string type, createtime as DataTime type and status as int. I want to select the record among all records whose status value is 10 and at the same time createtime is the most earlist. Any ideas how to write such query? BTW: ID is clust...

How to retrieve scalar value from stored procedure (ADO.NET)

Hello everyone, If in the stored procedure, I just execute one statement, select count(*) from sometable, then from client side (I am using C# ADO.Net SqlCommand to invoke the stored procedure), how could I retrieve the count(*) value? I am using SQL Server 2008. I am confused because count(*) is not used as a return value parameter of...

Using IsolationLevel.Snapshot but DB is still locking.

I'm part of a team building an ADO.NET based web-site. We sometimes have several developers and an automated testing tool working simultaneously a development copy of the database. We use snapshot isolation level, which, to the best of my knowledge, uses optimistic concurrency: rather than locking, it hopes for the best and throws an ...

Running a .sql script using MySQL with JDBC

I am starting to use MySQL with JDBC. Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql:///x", "x", "x"); stmt = conn.createStatement(); stmt.execute( "CREATE TABLE amigos" + "("+ "id int AUTO_INCREMENT not null,"+ "nombre char(20) ...

SQL: Change Date and group by Fiscal Month End

I have created the following table (via UNION ALL): ID Date Hour Weight Fiscal AAA 1/27/2009 0 10 0 AAA 1/30/2009 0 20 0 AAA 2/14/2009 0 10 0 AAA 2/18/2009 0 20 0 AAA 2/27/2009 0 20 0 AAA 2/28/2009 0 20 0 AAA ...

How could predicate pushing on an inline view slow down a query?

I have inherited a somewhat messy query that I am working on refactoring to be performant. During this process, one of the things I did due to personal preference was change all of the ANSI-99 join syntax from the "inner join" and "left outer join" statements to be predicates in the query. I noticed two very strange things that I wou...

connection string for remote database server

Hi guys, I have 2 servers setup at the moment. One is a web server running Win Server 08 and the other is a database server running SQL Server 08 on Win Server 08. Currently I have my site setup so that it can read/write to the database by using a connection string I created with the database name, server ip, db user and db pwd. The db...

conditional on duplicate key update

I'm trying to insert a new row, but if the key already exists, I want to update the row ONLY if a certain other value is in the table is different. Is this possible in a mysql query/statement? My table consist of the following columns: hat, mittens, name, last_update hat+mittens make up the unique index (say the values for "hat" and "m...

Creating a quicker MySQL Query

I'm trying to create a faster query, right now i have large databases. My table sizes are 5 col, 530k rows, and 300 col, 4k rows (sadly i have 0 control over architecture, otherwise I wouldn't be having this silly problem with a poor db). SELECT cast( table2.foo_1 AS datetime ) as date, table1.*, table2.foo_2, foo_3, foo_4, fo...

How do I generate random number for each row in a TSQL Select?

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row. SELECT table_name, RAND() magic_number FROM information_schema.tables I'd like to get a INT or a FLOAT out this. The rest of the story is I'm going to use the random number to create an random dat...

Passing datetime to a stored procedure & add a 1 sec to datetime

There are two things here i want to accomplish. List I want to pass a datetime into my stored procedure in the following format ('2007-05-28 00:00:00'), but that doesn't seem to work unless i use {ts '2007-05-28 00:00:00'} The next thing is I want to increase the @SEAL_DATE by a second so it can be used to check the dates between the e...

Incorrect syntax near the keyword 'table'.

I am receiving this error. What I am doing is trying to take data from one table and one db and place it into another db and table. The tables are not exactly the same. I am using a FETCH cursor, so I fetch the first row from db1 table and then place each column value into declared variables. Then I run the insert statement into db2 tabl...

SQL Server 2008 Hierarchy Data Type Performance?

How does SQL Server 2008's Hierarchy data type perform compared to using the hierarchy implementation described by Joe Celko here: http://www.intelligententerprise.com/001020/celko.jhtml? I've used Celko's method in the past with great results - but don't want to implement it for a new project unless it's better than what Microsoft has ...

sql async query problem....

So, why doesn't this ever make it to the callback function? using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace sqlAsyncTesting { public partial class Form1 : F...

Querying Oracle with a pick list

I have an oracle database that I have read-only access (with no permission to create temporary tables). I have a pick list (in Excel) of 28000 IDs corresponding to 28000 rows in a table which has millions of records. How do I write a query to return the 28000 rows? I tried creating a table in access and performing a join through ODBC b...