sql

Combining two T-SQL pivot queries in one

Suppose you had this table: CREATE TABLE Records ( RecordId int IDENTITY(1,1) NOT NULL, CreateDate datetime NOT NULL, IsSpecial bit NOT NULL CONSTRAINT PK_Records PRIMARY KEY(RecordId) ) Now a report needs to be created where the total records and the total special records are br...

Oracle: System table/view/proc which tracks proc/function/pkg invocation?

Is there a system table/view/procedure/whatever which can be used to determine the last time a proc, function, or packaged procedure was invoked? ...

MYSQL if statement (From mysql newb)

Is it possible to get this if statement to NOT print a column if EQ_Type!='ENGINE'? The empty column on my out put is bothering me. select if(EQUIPMENT.EQ_Type='ENGINE',ENGINE.Capacity,'') as Capacity, .... Thanks for your help. ...

Reproducible Deadlock Using Cascading Constraints

I am looking for code to generate a reproducible deadlock that occurs from the use of Cascading Constraints. I can find references online for the specific problem, and I have answered dozens of questions on deadlocks where cascading constraints were in use, but none of them has a reproducible version of the deadlock. I am not looking fo...

Best Linq2Sql equivalent of IsNull(Max(id))

I'm looking to convert the following SQL into Linq2SQL. select isnull(max(Id),0) from tbl Even though I have Id defined as Int NOT NULL I wish to be able to have a defult value of 0, even when there are no rows in the table. The best readable approach I've been able to come up with is var maxId = dc.tbl.Select(row => row.Id) ...

Does mysql enforce special syntax constraints on sql inside stored procedures? (select into problem)

I am often having a very hard time to create a stored procedure in mysql. Syntax which should really be fine just get not parsed within a stored procedure. Here is a simplified version that still doesn't get parsed. I am not able to turn this code into something that can be parsed. The update..set clause gives problems UPDATE I've sim...

Search entire table? PHP MySQL

Hello, I have made the following search script but can only search one table column when querying the database: $query = "select * from explore where site_name like '%".$searchterm."%'"; I would like to know how I can search the entire table(explore). Also, I would need to fix this line of code: echo "$num_found. ".($row['site_name']...

PHP how to run sql query one part at a time?

I have a table with roughly 1 million rows. I'm doing a simple program that prints out one field from each row. However, when I started using mysql_pconnect and mysql_query the query would take a long time, I am assuming the query needs to finish before I can print out even the first row. Is there a way to process the data a bit at a tim...

PL/SQL - use same pipelined function twice in the same query

I'm trying to use a pipelined function to save on time and reduce redundancy in my queries. The function in question returns data from a reference table based on some input. Records in the main data table I am selecting from have multiple columns that all refer to the reference table. The problem I run into is that when I try to use the ...

Stored Procedure WHERE LIKE ID or NAME

Group, I am trying to create a stored procedure using one variable @Customer. What I want to do is put something in my WHERE clause that says if it is a number search the CustomerID field where the number entered is LIKE CustomerID... If a char is entered search the CustomerName field where the text entered is LIKE CustomerName. Below ...

C# Read rows from a table and store the values in a string[]

I am using C# and ASP.NEt to create a web application where I need to insert/view data. I created my own class that stores values for a "Ticket" or a support ticket. My question is, how can I grab the values from the SQL table with the tickets and store them in the Ticket class? I don't need the specifics, just what SQL class I should us...

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My query is as follows, and contains a subquery within it: select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY ud DESC) The error I am receiving is weird it states Only ...

SQL Server Notifications - My OnChange does not fire.

Hey eveyone.. I would like to make use of SQL Server notifications to capture insert events at my database within a winforms app. I am attempting to use the SQLDependency object. The MSDN articles make this seem pretty straight forward. So I have created a little example application to give it a try. The event only seems to fire as I en...

Fetching 3 tables at a time, retrieve only the table which matches the condition (SQL)

I have 3 tables: WATER_TENDER,ENGINE and TRAILER. All of them have the EQ_ID as primary key and some other attributes, different for each table. for example, EQ _ ID='WT-123' points a row inside the WATER _TENDER table only, and nothing inside the other tables. As I do not know the EQ _ID in advance so I do not know which table to loo...

How can I include an ALTER VIEW statement in a transaction for a deploy script?

I'm running SQL Server 2005 on prod, but developing on 2008, and I need to alter a view to add a column. However I'm having trouble creating the deploy script because I need to wrap it in a transaction like this begin tran; alter view [dbo].[v_ViewName] with schemabinding as select ... /* do other stuff */ commit; When I do ...

SQL: Datatype choice for values -1, 0 and 1

Consider a table whose job is to store a rating of an object. The column of interest here is the one called RATING. The valid range of values to store is: 1 0 -1 The first thought was to store as a tinyint smallint. This would store only one byte two bytes per row. Given the tinyint's range of 0 to 255 smallint's range of -32768 to 3...

How to apply wildcard in instr() in MySQL?

Using a wildcard so all records will be matched. My code: if(empty($tag)) { $tag="%"; } mysql_query("select * from mytable where instr(tag,'$tag')>0") or die(mysql_error()); But it returns zero result. Even if if(empty($tag)) { $tag="*"; } It still returns zero result. How to resolve this problem? ...

How can I store images in BLOB?

Hi, I can not find useful code for how storing the images into BLOB ,please help me with some code and also can I show those images from MySQL to my desktop pane in GUI? ...

MySQL - How to find which sql query is being executed.

Hi Experts, How can i find which query is being executed in mysql. Example : I have a java application which makes several calls to the database, i want to track the queries executed by this application from the sql side. Thanks Micheal ...

Linq to SQL in ASP.Net MVC Runs as Network Service when using Impersonation

I have an ASP.Net MVC controller action that instantiates a DataContext object and I am currently passing the connection string directly into the constructor. I am using Impersonation and I have verified the current user in the controller action is the current Windows Auth. user of the web app, however when running a SQL Trace the query...