Question about accessing data
If two users are accessing same database table, How do we prevent users from overwriting each other’s data? Thank you, -Nimesh ...
sql
How do I get the last possible time of a particular day
Hi , I'm trying to achieve the last possible time of a particular day eg for Date of 2008-01-23 00:00:00.000 i would need 2008-01-23 23:59:59.999 perhaps by using the dateadd function on the Date field? ...
Share auto-incremented primary key between two tables
Hi I want to have two tables each have an INT "id" column which will auto-increment but I don't want either "id" columns to ever share the same number. What is this called and what's the best way to do it? Sequence? Iterator? Index? Incrementor? Motivation: we're migrating from one schema to a another and have a web-page that reads both...
SQL Server 2005 Using DateAdd to add a day to a date
How do I in SQL Server 2005 use the DateAdd function to add a day to a date ...
How do I declare a multi-column PK in MySQL
I'm trying to create a table with two columns comprising the primary key in MySQL, but I can't figure out the syntax. I understand single-column PKs, but the syntax isn't the same to create a primary key with two columns. ...
SQL Server: Check if table exists
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statement. When you Google for the answer, you get so many different answers. Is there an official/backward & forward compatible way of doing it? Here are two ways to start discussion: IF EXISTS (SELECT 1 FROM INFORMATIO...
How to INSERT an array of values in SQL Server 2005?
How do I write the SQL code to INSERT (or UPDATE) an array of values (with probably an attendant array of fieldnames, or with a matrix with them both) without simple iteration? ...
What is the best way to partition large tables in SQL Server?
In a recent project the "lead" developer designed a database schema where "larger" tables would be split across two separate databases with a view on the main database which would union the two separate database-tables together. The main database is what the application was driven off of so these tables looked and felt like ordinary tab...
Using Database entries to dynamically create a visio diagram
Is this possible? We have a Configuration Management Database that stores information such as our servers, what datacentre they're stored in, applications that reside on them, as well as interfaces that send data from one application to another. We would like to use Visio to connect to our SQL 2005 database, and automatically generat...
Obfuscate / Mask / Scramble personal information
I'm looking for a homegrown way to scramble production data for use in development and test. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I've come up against a wall trying to scramble customer names. I want to keep real names so we can still use or searches so r...
Improving code readability for SQL commands
In one of my classes for a web application I am developing, I have some reasonably long SQL queries. When developing a three-tier application, what are the best practices to make this kind of code neater? Dim dc As New SqlCommand("INSERT INTO Choices VALUES ('" + _ SanitizeInput(strUser) + "', '" + _ ...
Not getting the correct count in SQL
Hi, I am totally new to SQL. I have a simple select query similar to this: SELECT COUNT(col1) FROM table1 There are some 120 records in the table and shown on the GUI. For some reason, this query always returns a number less than the actual count. Can somebody please help me? ...
Is it possible to query a tree structure table in MySQL in a single query, to any depth?
I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query More specifically, given a tree structured table (id, data, data, parent_id), and one row in the table, is it possible to get all descendants (child/grandchild/etc), or for tha...
SQL Server 2005 Setting a variable to the result of a select query
How do I set a variable to the result of select query without using a stored procedure? ...
Benefits of using a Case statement over an If statement in a stored procedure?
Hi are there any pros / cons relating to the speed that a stored procedure executes when using an IF statement or choosing to use a CASE statement instead? ...
Set a database value to null with a SqlCommand + parameters
I was previously taught today how to set parameters in a SQL query in .NET in this answer (click). Using parameters with values are fine, but when I try to set a field in the database to null I'm unsuccessful. Either the method thinks I am not setting a valid parameter or not specifying a parameter. e.g. Dim dc As New SqlCommand("UPDA...
Executing a stored procedure within a stored procedure
Hi, I would like to execute a stored procedure within a stored procedure eg EXEC SP1 BEGIN EXEC SP2 END But I only want SP1 to finish after SP2 has finished running so I need to find a way for SP1 to wait for SP2 to finish before SP1 ends. Thanks ...
Stored procedure not being executed within another stored procedure
Hi, I have found that SP2 doesn't execute from within SP1 when SP1 is executed. Below is the structure of SP1: ALTER PROCEDURE SP1 AS BEGIN Declare c1 cursor.... open c1 fetch next from c1 ... while @@fetch_status = 0 Begin ... Fetch Next from c1 end close c1 deallocate c1 exec sp2 end ...
Either OR non-null constraints in MySQL
What's the best way to create a non-NULL constraint in MySQL such that fieldA and fieldB can't both be NULL. I don't care if either one is NULL by itself, just as long as the other field has a non-NULL value. And if they both have non-NULL values, then it's even better. ...
Reset @@FETCH_STATUS stored procedure
Hi, How can I reset the @@FETCH_STATUS variable or set it to 0 in a stored procedure? ...