I'm working on a system which has to handle a number of race-conditions when serving jobs to a number of worker-machines. The clients would query the system for jobs with status='0' (ToDo), then, in an atomic way, update the 'oldest' row with status='1' (Locked) and retrieve the id for that row (for updating the job with worker informat...
Hi! Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails? Best Regards Oliver Hanappi ...
Hi all, I've got a web application that matches images to tags, and I need to create a way of dynamically refine results for tag search. However, I cannot find a clean way to make that SQL queries, and that's where I need your help. The idea is that if I search for tags "clean" and "dog", I will have image results that have both the ta...
I want to do this SELECT * FROM sales_order_header WHERE order_reference LIKE @order_reference + '%' AND ((@booked_in IS NULL) OR ( booked_in = @booked_in)) AND ((@depot_assigned IS NULL) OR ( IF @depot_assigned = 'Y' depot_code <> ' ' ELSE depot_code = ' ') ) I'm sure you can all guess the OR ( IF @depot_assigned etc ...
I need to be able to validate a given username and password against Active Directory and return whether that user exists. My setup is that I have two web servers in a DMZ, and then a SQL Server in the LAN. The sales people have a admin panel that is on the web servers. When they login to the admin panel, I want it to run a stored proc...
Each item in c_data is in a category/section. I would like to limit how many items are displayed per category, rather than limiting the total number of items retrieved. Obviously if I add something like "limit 20" to the query, it will only fetch 20 results in total, rather than 20 results per category. SELECT cm.id, ...
Hi guys, I'm trying to find out if this is possible, but so far I haven't found out any good solutions. What I would like to achieve is write a stored procedure that can clone a database but without the stored data. That means all tables, views, constraints, keys and indexes should be included but without any data. Can it be done? ...
hi, I have the following table in an Oracle DB id date quantity 1 2010-01-04 11:00 152 2 2010-01-04 11:00 210 1 2010-01-04 10:45 132 2 2010-01-04 10:45 318 4 2010-01-04 10:45 122 1 2010-01-04 10:30 1 3 2010-01-04 10:30 214 2 2010-01-04 10:30 5515 4 2010-01-04 10:30 ...
I'm querying across two dbs separated by a legacy application. When the app encounters characters like, 'ü', '’', 'ó' they are replaced by a '?'. So to match messages, I've been using a bunch of 'replace' calls like so: (replace(replace(replace(replace(replace(replace(lower(substring([Content],1,153)) , '’', '?'),'ü','?'),'ó','?'), 'é'...
Hello! I have a PostgreSQL database with two tables named "user" and "group". They have different columns, but I want to join them. user: id, firstname, lastname, email, registrationdate group: id, name, desc, createdate I can do two separate queries: select * from "user" where ... order by "registrationdate" asc; select * from "grou...
I have a database at location xmachina\sqlexpress named News, News has a table NewsTable. How do I tell ASP to look at News database? Is it part of the SelectCommand? Or a webconfig variable? Right now I get a [SqlException (0x80131904): Invalid object name 'NewsTable'.] web.config <add name="NewsTableConnectionString" connection...
Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures? Consider a stored procedure whose job is to check/update a value of a record based on its StatusID or some other FK lookup table or range of values. Consider a Status table where the ID is most important, as...
Is there an equivalent to cron for MySQL? I have a PHP script that queries a table based on the month and year, like: SELECT * FROM data_2010_1 What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table. I was wondering if I c...
We have a database, that through a hiccup in a script, has one table that doesn't match the schema of it's sister database tables. I have rearranged the table to match the schema of the other tables, but would like to strongly bind the name value pairs just in case the possibility arises of other databases having the same issue. Curren...
I currently have this query SELECT short_url, type, file_thumbnail, file_embed, media.id, user, media.file_url, votes, comments, GROUP_CONCAT(info.itemTime) AS time, info.title, media.time AS oldTime, media.title, info.topic, ...
I'm trying to use SqlBulkCopy to import a bunch of data to our website. In most of the other areas we're using Entity model which uses byte arrays to represent binary data in SQL. However, SqlBulkCopy seems to be confusing byte[] with string. Everything seems to be working fine except for this one binary column which throws an exception:...
I understand the three basic failures in EAV, namely that it takes a lot of work to reassemble the data. However, I want a database where I can add custom fields. A lot of people say that Virtuemart allows custom fields but without using an EAV database structure. Can someone explain how this can be done or provide links? ...
Error Message: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND domain = 'ard.qc' AND snapshot_id = 2010 AND locale = 'en_US'' at line 1 SQL Query: SELECT entity_id, content_id FROM collateral_cms_mapping WHERE entity_id IN ({$en...
My issue at hand is that i need to remove about 60M records from a table with out causing deadlocks with other processes that use this table. At this point i'm almost done removing the records using a while loop that only processes about 1M records at a time however its taken all day. Q1: What is the optimal way to remove large quantit...
Is there a way to coerce the result row gotten from calling a stored procedure into a specific object so I can pass just a list of that object into the view? I know I can use things like Node.list() to do this, but I am eventually going to replace getnodes() with a fairly complicated stored procedure that creates temp tables and does so...