sql

How to join dynamic sql statement in variable with normal statement

I have a quite complicated query which will by built up dynamically and is saved in a variable. As second part i have another normal query and i'd like to make an inner join between these both. To make it a little more easier here is a little example to illustrate my problem. For this little example i used the AdventureWorks database. ...

set up way of getting mysite.$domain

Hi I have several domains, only one website and one databse table for each domain. example: wbesite.us - data from USA goes to database table main_usa wbesite.co.uk - data form UK goes to database table main_uk Only have one database with name of the website. Having only one website structured, and having variables like $sql="select...

Using Domain Administrator account for SQL Server

Is it wise to use Domain Administrator as a SQL Windows Authentication log-in? ...

Speeding up PostgreSQL query where data is between two dates

I have a large table (> 50m rows) which has some data with an ID and timestamp: id, timestamp, data1, ..., dataN ...with a multi-column index on (id, timestamp). I need to query the table to select all rows with a certain ID where the timestamp is between two dates, which I am currently doing using: SELECT * FROM mytable WHERE id = ...

Storing SQL Queries within a Configuration file?

Hello y'all, A general question on what everyone's opinion is on storing SQL queries within a configuration file? Is this just another bike shed? Cheers, Ben ...

SQL Server - stored procedure suddenly become slow

I have written a stored procedure that, yesterday, typically completed in under a second. Today, it takes about 18 seconds. I ran into the problem yesterday as well, and it seemed to be solved by DROPing and re-CREATEing the stored procedure. Today, that trick doesn't appear to be working. :( Interestingly, if I copy the body of the sto...

How can I provide values for non-grouped columns in NHibernate?

I have a criteria query: Session.CreateCriteria<Sell043Report>() .SetProjection(.ProjectionList() .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.agent)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.cusip)) .Add(LambdaProjection...

SQL joining 3 tables when 1 table is emty

I am trying to write a query that connects 3 tables. The first table is info about each festival. The second table is the number of votes for each festival. The third table is reviews for each festival. I want to join all 3 tables so I get all the columns from table1, join table1 with table2 on the festivalid, but I also need to count ...

Insert multiple values using INSERT INTO (SQL Server 2005)

In SQL Server 2005, I'm trying to figure out why I'm not able to insert multiple fields into a table. The following query, which inserts one record, works fine: INSERT INTO [MyDB].[dbo].[MyTable] ([FieldID] ,[Description]) VALUES (1000,N'test') However, the following query, which specifies more t...

My SQL query is only returning children I need the parent aswell

My sql query is only returning the children of the parent I need it to return the parent as well, public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, paren...

How to make additional columns on pivot of sql table in SQL Server 2005?

After getting such a great feedback from my last question. Maybe someone can help me with this problem also. I have to rotate a given table from an SQL Server but a normal pivot just doesn't work (as far as i tried). So has anybody an idea how to rotate the table into the desired format? Just to make the problem more complicated, the l...

free sql script to get a list of countries, provinces/states and their cities.

I am working on a registration page for a php website as mysql as the backend database. I need a sql script to insert the list of countries with their associated provinces and the provinces with their associated cities. I need all the countries, provinces and cities all over the world which are related to each other. I can get the indi...

SQL Query Help: how to select just start and end rows of groups (Oracle)?

(Apologies for the title of this question - I wasn't overly sure how to explain it) Not sure whether this can be done in SQL. Below is a (somewhat truncated) sample of an event log table. EVENT ID DATE TIME --------- ---------- -------- ---- ONE_THING 0006241800 20091109 1719 ONE_THING 0006944800 20091109 1...

PL/SQL embedded insert into table that may not exist

Hi, I much prefer using this 'embedded' style inserts in a pl/sql block (opposed to the execute immediate style dynamic sql - where you have to delimit quotes etc). -- a contrived example PROCEDURE CreateReport( customer IN VARCHAR2, reportdate IN DATE ) BEGIN -- drop table, create table with explicit column list CreateReportTa...

Rails: Using to_param for SEO-friendly slugs screwing up database calls?

So in my Rails models I'm doing the following to generate an "SEO friendly" slug: def to_param "#{id}-#{title.parameterize}" end So that generates something like: example.com/items/1-example-title But when I check my logs, the SQL calls are then: SELECT * FROM `items` WHERE (`items`.`id` = '1-example-title') LIMIT 1 That seems t...

Display sql results in groups

Hi all, I have an sql query here and it returns a number of results. I'd like to show these results in groups. What I mean is, show the first 20 results in some part of the page, show the next 20 results in another part of the page etc... How can I do that? *I'm using PHP to display results. ...

STORED PROCEDURE working in my local test machine cannot be created in production environment.

Hi, I have an SQL CREATE PROCEDURE statement that runs perfectly in my local SQL Server, but cannot be recreated in production environment. The error message I get in production is Msg 102, Level 15, State 1, Incorrect syntax near '='. It is a pretty big query and I don't want to annoy StackOverflow users, but I simply can't find a solu...

What is the fastest way for me to take a query and turn it into a refreshable graph of the results set?

I often find myself writing one off queries to either answer someone's question or trouble shoot something and I would like to be able to quickly expose the on demand refreshable results of the query graphically so that I can share these results to others without having to go through the process of creating an SSRS report and publishing ...

Bulletproof way to DROP and CREATE a database under Continuous Integration.

I am attempting to drop and recreate a database from my CI setup. But I'm finding it difficult to automate the dropping and creation of the database, which is to be expected given the complexities of the db being in use. Sometimes the process hangs, errors out with "db is currently in use" or just takes too long. I don't care if the db i...

SQL Server: Is it possible to prevent SQL Agent from failing a step on error?

I have a stored procedure that runs custom backups for around 60 SQL servers (mixes 2000 through 2008R2). Occasionally, due to issues outside of my control (backup device inaccessible, network error, etc.) an individual backup on one or two databases will fail. This causes this entire step to fail, which means any subsequent backup comm...