sql

What SQL would I need to use to list all the stored procedures on an Oracle database?

What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I'd like two queries: list all stored procedures by name list the code of a stored procedure, given a name ...

How to send a Reporting Services multi-value parameter to a SQL Server function?

Hi, I have a report that use a multi-value parameter into a "in" statement in a query. For example (with @areas as the multi-value parameter): select * from regions where areas in (@areas) It works perfectly but now I need to send the same parameter to a function in the SQL Server 2005 database: select name, myFunction(@areas) from r...

Sql group rows with same value, and put that value into header?

I want to group rows with SQL, my result set is following name  size  date data1  123  12/03/2009 data1  124  15/09/2009 data2  333  02/09/2010 data2  323  02/11/2010 data2  673  02/09/2014 data2  444  05/01/2010 I want to group result set like this one: data1   123  12/03/2009   124  15/09/2009 data2   333  02/09/2010   323  02/11/2...

Can I define an in-cycle variable in T-SQL SELECT (like LET in LINQ)?

I can write something like this with LINQ: var selection = from person in personList let initials = person.FirstName[0] + person.LastName[0] select initials; Can I do something similar with SQL, like maybe: SELECT @Initials FROM [Person] SET @Initials = SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (P...

SQL Server: Terminate SQL From Running

Is there away to cause a script to prevent running if an if statement is true even with "GO"'s? For example I want to do something similar to the following: insert into table1 (col1, col2) value ('1', '2') GO if exists(select * from table1 where col1 = '1') BEGIN --Cause Script to fail END GO insert into table1 (col1, col2) val...

SQL: Find rows where field value differs

I have a database table structured like this (irrelevant fields omitted for brevity): rankings ------------------ (PK) indicator_id (PK) alternative_id (PK) analysis_id rank All fields are integers; the first three (labeled "(PK)") are a composite primary key. A given "analysis" has multiple "alternatives", each of which will have a "...

Outer join with conditions?

I'm trying to look up two pieces of metadata (volume and issue) on items which may have either a volume, issue, or both. The metadata is stored in a table with item ID, key (metadata field ID) and value. This does work, but it seems overly complex and repetitive: select volume.text_value as volume_value, issue.text_value as issue_valu...

Script all data out of a single table or all tables in Sql Server 2005/8?

Hey folks, Simple question here, and I know there are lots of kludgy ways to do it :) But I was hoping a SQL server expert had a script to easily script all the data out of a table? Or possibly all the tables in a DB? I'm getting tired of copying and pasting data over RDC! :) ...

Microsoft Access SQL query

Can someone please help me out with a query? In Microsoft SQL Server, I have the following query, which executes fine: SELECT * FROM ControlPoint INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID INNER JOIN Site ON Site.SiteID = Project.SiteID WHERE Project.ProjectName LIKE '%Flood%' My problem is, when I try to execute...

Can you do a count query inside another query and use the results?

Could I run the query: Select id, ( select count(*) from tableA as a where a.value < a.id ) from tableA as a where id < 5 and get the results I wanted. If not is there a way to do the same thing without having to do 2 querys? ...

How can I count this when there are no rows?

I am trying to count the number of days of the current week that do not have an event associated with them, but I don't know how to do it. For example, I count the number of events happening this week with this query: SELECT COUNT(e.event_id) FROM cali_events e LEFT JOIN cali_dates d ON e.event_id = d.event_id WHERE YEARWEEK(d.date) = ...

SQL scripts under Subversion

I am a build engineer and responsible for our source control layout. We need to keep a version of all database objects and also group changes together with rollback scripts for pushes to production. We have development, QA, and Production environments and there are different versions of the database objects in each. One area we have t...

When using FREETEXTTABLE in ms sql server how do you search on the primary key?

I have a query using a FREETEXTTABLE full text search which works perfectly for every column included in the index except for the primary key. The primary keys are in a format like abcdef123456 and when you search for abcdef123456 you get that one record returned. However, if you search for abcd or 12345 you get no results (unless that...

Intelligent Searching with wildcards (*) and word grouping in SQL Full-text Search

What's the best way implement MS SQL full-text search using all the normal things like wildcards and quotations. For example: If the search term the user inputs is Overdose of "Vitamin C" for child* I would like to treat "Vitamin C" as one phrase and would like to match "child" and "children" The documentation offers so many al...

Importing XML data into MS SQL server programmatically

I have 5 large XML files which I am keen to analyse. All of them are too large to open in a text editor and so I do not know their XML schemas. I have tried to import them into SQL server, however the process has given me an error even though I am pretty sure they are valid, as they were sourced from very reputable programmers. I have ...

How do I insert multiple values into a single column using sqlite?

I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work. Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category i...

MySQL Database Design Optimized for Data Retrieval in PHP

Hi all, I am a fairly new MySQL developer and am starting on a project that I could do with a bit of initial advice on... I am creating a database that will primarily be holding a certain number of items (between 1-5k) and around 40 boolean variables associated with each one. Users will then be inputting their choice of these 40 value...

What is the best way to handle multiple database connections in C#

If say I need to run two separate SQL statements against two separate databases. Right now I do (pseudocode): Try{ declare variable connectionA to DatabaseA declare variable connectionB to DatabaseB connectionA.open() connectionB.open() declare variable SQLCmdA with ConnectionA and one SQL statement declare variable SQLCmdB with...

Getting nonsensical results from SQL query

Hi, I've encountered a very, very strange situation in my application. I say strange because the errors are intermitent and I cannot find out why they occur. I've pass a sqlcommand to the DB (Sql 2005) trying to get a bigint (column ID_Facultate) from a single table. The problem is that I've got a string (the name of some lecture) that...

Make one ID with auto_increment depending on another ID - possible?

I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this: (TicketID, ProjectID) (1, 1) (2, 1) (3, 1) (4, 2) (5, 2) (6, 3) ...