It's easier with an example. I have two tables: Books and Chapters. Both have Title and Id columns. Chapters also has a Book column which is a foreign key to Books.Id. Books.Title is unique (I don't want two books with the same title). Now my problem is defining Chapter.Title uniqueness. I want it to be unique as long as Books.Id is the...
Is it at all possible to have a table valued function in MSSQL that takes an attribute and generates an associated SQL statement with Pivot function? CREATE FUNCTION dbo.fnPivot (@EntityTypeID int) RETURNS TABLE AS BEGIN DECLARE @SQL varchar(MAX); DECLARE @COLS varchar(MAX); select @COLS=coalesce(@COLS+',','')+'['+Name+']'f...
Let's say I have a table with two columns, where a user places a booking. datefrom dateto ------------------------------------------- 2009-05-23 00:00:00 2009-05-27 00:00:00 How would I use SQL to return how many days the user will be booking for? All I want is to return the number of days. ...
I've set up a test database to begin unit-testing an app that I've recently been added to. It is a 3-tier design (presentation layer, BOL and DAL) and this is my first time writing unit tests. I've decided to start at the BOL and I figure the best way is to swap out the ConnectionString (which has been passed along as a Shared String...
I have the query below which is causing me issues. It is totaling fine however as I have different service dates its grouping by distinct then servicedate, but I just want distinct districts with the totals next. How can I get round this as I'm confused now!!! SELECT DISTINCT(DISTRICT), Sum(completed) as TotalCompleted, MON...
How to Create a Table from the recordset value sqlCardEvent1 = "select * from tmp_CARDEVENT" If rsCardEvent1.State = 1 Then rsCardEvent1.Close rsCardEvent1.Open sqlCardEvent1, Cn, adOpenStatic, adLockOptimistic cmdCardEvent1.CommandText = sqlCardEvent1 Set rsCardEvent1 = cmdCardEvent1.Execute sql33 = "create table t...
I have to deal with a table where there is a set of fields each followed by a second field that will hold a suggested new value until this change is confirmed. It looks a little like this: refID field1 newField1 field2 newField2 ... refID is an ID value that links to a master table. One row in the master table can have ...
I've seen queries for 2005/2008 to find what process caused a delete but have not seen anything for 2000. Is there anything? ...
I'd like to generate insert-strings for a row in my Oracle database including all its dependent rows in other tables (and their dependent rows). Example: CREATE TABLE a ( a_id number PRIMARY KEY, name varchar2(100) ); CREATE TABLE b ( b_id number PRIMARY KEY, a_id number REFERENCES a(a_id) ); When I extract the row from a wit...
Let's say I have a MS-SQL 2005 table named "People" with the following rows: |FirstName|LastName| |JD |Conley | |Joe |Schmo | |Mary |Jane | I want to execute a SQL statement like: select * from People where FirstName > 'JD' The problem I'm having is I can't think of a way to get LINQ to SQL to generate this SQL...
In a table, I have a column called MEMO_TEXT that is a text data type. When I try creating a view and use a GROUP BY, I get the following error: SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. I get this error when I try to do a DISTINCT on the ...
I wanna try to use PgQ, but can't find any documentaion how to install it on my database. Do I need to install some stored procedures or something like that? ...
I'm just getting into using VS2008 to write clr stored procedures for SQL 2008. When writing c# code I am used to having a separate 'Test Project' where I would place all my unit testing code, however it appears at first blush that I can't have the same setup with a clr sql project with stored procedures. It 'feels' like this can be do...
As a junior programmer I think it may be beneficial to learn solid database design. What books would you recommend on learning database design? Just to be clear, the specific aspect of design I am referring to is creating a collection of tables and their relationships. Note: The programming books I have enjoyed reading (gaged by teachin...
Is there a way to run a query and then have SQL Server management studio or sqlcmd or something simply display the datatype and size of each column as it was received. Seems like this information must be present for the transmission of the data to occur between the server and the client. It would be very helpful to me if it could be dis...
9 months later, the same problem shows up again. I've tried everything I can think of: I cast as varchar(max) on the stored procedure; I changed the mapping; tried to find the collection linq works with, but couldn't find anywhere; I have been running this stored procedure for some time Mapping it to my objects using LINQ to SQL, and...
I have an Oracle table with data that looks like this: ID BATCH STATUS 1 1 0 2 1 0 3 1 1 4 2 0 That is, ID is the primary key, there will be multiple rows for each "batch," and each row will have a status code in the STATUS column. There are a bunch of other columns, but these are the important...
I've got a table of 5,651,744 rows, with a primary key made of 6 columns (int x 3, smallint, varchar(39), varchar(2)). I am looking to improve the performance with this table and another table which shares this primary key plus an additional column added but has 37m rows. In anticipation of adding a column to create the hash key, I did...
I have a SQL Server query that I need to convert to MySQL. I've never used SQL Server/T-SQL before, so I have no experience with FOR XML PATH. There's surprisingly little documentation on this sort of thing. If I remove the FOR XML PATH statement, MySQL returns the error "Operand should contain at least 1 column(s)." It seems like the n...
Hello, I can't seem to figure out how this is happening. Here's an example of the file that I'm attempting to bulk insert into SQL server 2005: ***A NICE HEADER HERE*** 0000001234|SSNV|00013893-03JUN09 0000005678|ABCD|00013893-03JUN09 0000009112|0000|00013893-03JUN09 0000009112|0000|00013893-03JUN09 Here's my bulk insert statement: ...