I've lately seen a database where there was a table Types with columns Id, Key and Name. Id was just an Id of the type, Key was a short key name for the type, for example "beer", and the Name was text that could be displayed for the user (for example, "Our greatest beers"). Id was of course unique and was a primary key for this table....
I have a SQL table with a date field defined as char(8), or 20090609, and a time field defined as char(4), or 1230. I am moving this data into another table and I want to combine the two fields and put them in a smalldatetime field in the new table. My query is like this: INSERT NewTable(eventdate) SELECT CAST((datecol + ' ' + substr...
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table? ...
I have a MySQL member table, with a DOB field which stores all members' dates of birth in DATE format (Notice: it has the "Year" part) I'm trying to find the correct SQL to: List all birthdays within the next 14 days and another query to: List all birthdays within the previous 14 days Directly comparing the current date by: (D...
I have added a bounty to this as I have, as yet been able to figure this out and time is out. The below stored procedure will not allow me to add it to Modify it. When attempting to modify it I get the following error --> Msg 213, Level 16, State 1, Procedure spPersonRelationshipAddOpposing, Line 51 Insert Error: Column name or n...
Here is the problem I am trying to solve. I have a table where I store a StartExclusionDate and EndExclusionDate and set an ApplyDate for my object (called Exclusion). There can be many ExtId entries in the table. I want to get the ApplyDate from the last continuous record where a date passed to the query is included between the Start...
Suppose I have a table named [ProductPriceHistory] like the following: HistoryID..ProductCode..EffectDate.... Price.... IsActive...ProductName 1----------11-----------1 Jan 09-------100-------true-------AAA 2----------11-----------1 Feb 09-------150-------true-------AAA 3----------11-----------1 Mar 09-------200-------false------AAA 4-...
I need to pass a % character as part of a parameter in a dts package. The package just errors saying invalidenvironment variable specification. Does anyone have any ideas? ...
I'm building a web app that attempts to install/upgrade the database on App_Start. Part of the installation procedure is to ensure that the database has the asp.net features installed. For this I am using the System.Web.Management.SqlServices object. My intention is to do all the database work within an SQL transaction and if any of it ...
Hi, I am starting development on a medium-scale C++ project that has to work with a Microsoft SQL Server database. I have done database work before using .NET technologies but I don't find using a .NET approach to be appropriate this time. I would like to get some feedback concerning the various free (as in GPL, LGPL, Boost) C/C++ SQL l...
We have an application that is for both internal users and external customers. We would like to authenticate against AD for the internal users and against sql membership for the external customers. Has anyone taken a similar approach? Also what is the best way to authenicate against AD when in a DMZ? I would rather have a proxy of some s...
Hey guys i wanted to know, will i run into any concurrency problem with this? This is NOT in a transaction. This code is for Sqlite(prototype), but i plan to use it with either MySql or a SQL from MS command.CommandText = "UPDATE tag_name SET count = count+1 "+ "WHERE tagid=@tagid"...
I am pretty sure this code is fine. I wanted to know what you guys think about the insertMediaTags function (2nd func). The things i am worried about is the below concurrent safe? and if insertMediaTags is optimize enough? note it is in a transaction due to first func but it is also in a loop which could mean its bad? I am open to any c...
Does anybody have a script for inserting a list of UK towns cities into a SQL server database? ...
I got the following error trying to convert from a varchar to varbinary.. Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. and this is the alter command that I tried. alter table foo Alter column bar varBINARY(max) So how do I use the convert function...
I'm trying to do something like : SELECT * FROM table LIMIT 10,20 or SELECT * FROM table LIMIT 10 OFFSET 10 but using SQLServer The only solution I found looks like overkill: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10 I also found: SELECT TOP 10 * ...
I am writing a number of scripts to update numerous Access tables. I would like to add a column to each that has a field called "date_created" that is a timestamp of when the record is created. Doing this through the table view is simple, simply set the DefaultValue = now(). However, how would I accomplish this in sql? This is my curren...
I'm using aspect-oriented programming techniques to create very fine-grained logging for a class library. Each method call will be logged. I'm trying to develop a stacked bar chart with each call that comes from an instance of a class, with each sub-call occupying space in between the start and finish of the outer call to visually repr...
I want to do a simple versioning system but i don't have ideas on how to structure my datas, and my code. Here is a short example: User logs in User has two options when uploading a file: Submit a new file Submit a new version of a file Users should be able to see the tree. (the different version) The tree can only be up to 2 leve...
I have a bunch of tables in a relational database which, obviously, are dependent upon one another due to foreign key relationships. I want to build a dependency tree, traverse it, and output INSERT SQL statements. I need to first output SQL for foreign key tables in my dependency tree first because parent tables will depend on values fr...