Sometimes we deploy applications behind customer firewall and we need read only access to their DB for debugging issues as sometimes their IT people are not SQL savvy. We want to bundle our application with some web based application that will expose the database and allow us to fire adhoc SQL queries and show their output in HTML table....
I'm just wondering what the optimal solution is here. Say I have a normalized database. The primary key of the whole system is a varchar. What I'm wondering is should I relate this varchar to an int for normalization or leave it? It's simpler to leave as a varchar, but it might be more optimal For instance I can have People =========...
I am using Oracle SQL (in SQLDeveloper, so I don't have access to SQLPLUS commands such as COLUMN) to execute a query that looks something like this: select assigner_staff_id as staff_id, active_flag, assign_date, complete_date, mod_date from work where assigner_staff_id = '2096'; The results it give me look something like this: S...
I have an application that is causing a lot of headaches. It's a .NET app connecting to SQL Server 2005 via a web service. The program has grid that is filled by a long running stored procedure that is prone to timing out. In the case when it does time out and a SqlException is thrown, there is no execption handling to close the connecti...
I've got a sections table, and an items table. The problem is each item may be in one or more sections, so a simple 'section_id' for each item won't work, and sql doesn't have a way to store arrays where I can do say "WHERE 5 in section_ids"... I've considered storing the list of ids as a comma separated string, the problem is I see no...
I have a SQL table with news stories and Unix timestamps. I'd like to only keep the 50 newest stories. How would I write an SQL statement to delete any amount of older stories? Edit: See my answer ...
How can i extract the list of available SQL servers in an SQL server group? Im planning to put that list in a combobox in vb.net. Thank you. ...
I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they are...
Anyone have any idea? And any open source sofware which also seens to perform this kind of functionality? ...
Is it possible to find out who called a store procedure. So if you get an error in proc3, I know if it was called by proc1 or proc2. I'm using SQL Server 2005. ...
In SQL, how do update a table, setting a column to a different value for each row? I want to update some rows in a PostgreSQL database, setting one column to a number from a sequence, where that column has a unique constraint. I hoped that I could just use: update person set unique_number = (select nextval('number_sequence') ); but i...
Hi, I'm currently generating SQL insert statements from more than one tables, and in the generated data I need to use a CASE statement, like this: select 'INSERT INTO TABLE1 (f1, f2, f3, f4 ...) values (' ||t.f1||',' ||CASE WHEN t.f2 > 0 THEN '1' ELSE '0' END CASE from table2 t , table...
I am am trying to load a SQL table from a flat file. The flat i am talking about is a comma separated file. This has all the data required to populate a table will each column separated by a comma ",". I need some way by which i can load this content into the table faster. ...
In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't able to limit the field length at the database level) ...
Selecting the union: select * from table1 union select * from table1_backup What is the query to select the intersection? ...
Part of the setup routine for the product I'm working on installs a database update utility. The utility checks the current version of the users database and (if necessary) executes a series of SQL statements that upgrade the database to the current version. Two key features of this routine: Once initiated, it runs without user intera...
I'm not sure if this is something I should do in T-SQL or not, and I'm pretty sure using the word 'iterate' was wrong in this context, since you should never iterate anything in sql. It should be a set based operation, correct? Anyway, here's the scenario: I have a stored proc that returns many uniqueidentifiers (single column results)....
This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end. I speak of dynamic sor...
Hello to everybody that is smarter than me! I have a curious question about efficiency. Say I have a field on a database that is just a numeric digit that represents something else. Like, a value of 1 means the term is 30 days. Would it be better (more efficient) to code a SELECT statement like this... SELECT CASE TermId ...
Is there a clean way of cloning a record in SQL that has an index(auto increment). I want to clone all the fields except the index. I currently have to enumerate every field, and use that in an insert select, and I would rather not explicitly list all of the fields, as they may change over time. ...