I have a SQL-Server 2008 database and a schema which uses foreign key constraints to enforce referential integrity. Works as intended. Now the user creates views on the original tables to work on subsets of the data only. My problem is that filtering certain datasets in some tables but not in others will violate the foreign key constrain...
I've used a table valued parameter before, but I'm not sure how to use xml.
I do not know the best way to format my xml but I think I would try this:
<Car>
<Name>BMW</Name>
<Color>Red</Color>
</Car>
Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with ...
I have an SQL query which I need to translate to Linq to SQL. I was successful until it came to Sums and I'm completely stuck there. I know how to do single/simple groupings and sums but this time I desperately need help. I'd appreciate any ideas.
Table relations are like this:
TreeNodes - one-to-many - Dispatches (not every TreeNode h...
I have a quick question about a select statement condition.
I have the following table with the following items. What I need to get is the object id that matches both type id's.
TypeId ObjectId
1 10
2 10
1 11
So I need to get both object 10 because it matches type id 1 and 2.
SELECT ObjectId
FROM Table
WHERE TypeI...
I have a bunch of tables and I need to create basic INSERT stored procedures for all of them.
Does anyone have anything that does this or a good start to do this?
...
is this possible that i create a stored procedure that generates content of defined table to a path that i passed as its(stored procedure) parameter?
...
I have declared a variable @date
SELECT @date = CASE
WHEN MAX(dt) IS NULL THEN '31/12/2009'
ELSE MAX(dt) + 1
END
FROM mytab
A code snippet where I am using the local variable
CASE
WHEN MAX(DateValue)= @date THEN NULL
ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103)
END AS newdt
I don't wa...
Sorry about the title, not sure how to describe without example. I trying to implement faceting of attributes in SQL Server 2008.
I have 2 tables. itemAttributes and facetParameters
Assume the following values in itemAttributes
id, itemId, name, value
---------------------------------------
1 1 keywords example1...
I'll be doing this in sqlite now that they support foreign keys and tsql and perhaps mysql.
Is it illegal to do something like
CREATE TABLE comment(
id integer primary key,
parent integer references(comment.id),
author integer references(User.id),
desc varchar(max),
hidden bit
deleted bit
);
where parent may be 0 or null because it ...
Hello...
when I need the columns of an existing table I use the query:
SELECT c.[name]
FROM
(SELECT * from syscolumns) c
INNER JOIN
(SELECT [id] from sysobjects where name= 'tableName') o on c.[id]=o.[id]
I need the fields of a table that I create during runTime:
select
a.ID,
b.lName,
b.fName
into #T
fro...
My SQL Server table has, between other, these columns.
AutoID which has IdentitySpecification set to True and GuidKey which has the default value of (newid())
AutoID GuidKey
1 f4rc-erdd
2 gedd-rrds
Is there any way of getting the GuidKey from the row inserted?
I need something like the Scope_Identity(), with the di...
In the vs2008 query builder, I’m trying to make a query that gets a parameter for the "TOP" Command, but it won't let me, and it say's "Error in top expression"
Works:
SELECT TOP 5 * FROM dbo.SomeTable
WHERE SomeColumn = SomeValue
Doesn't Work:
SELECT TOP @param1 * FROM dbo.SomeTable
WHERE SomeColumn = SomeValue
...
Not sure where to start on this one -- not sure if the problem is that I'm fooling the query optimizer, or if it's something intrinsic to the way indexes work when nulls are involved.
One coding convention I've followed is to code stored procedures like such:
declare procedure SomeProc
@ID int = null
as
select
st.ID,st.Col1,st....
In Microsoft SQL Server 2005 and .NET 2.0, I want to convert the current date to a string of this format: "YYYY-MM-DD". For example, December 12th 2009 would become "2009-12-20". How do I do this in SQL.
The context of this SQL statement in the table definiton. In other words, this is the default value. So when a new record is creat...
Hi folks:
I try to implement the following link with a scalar-valued function, the SQL Server 2000 return an error msg: Invalid use of 'newid' within a function.
http://www.bennadel.com/blog/310-Ask-Ben-Getting-A-Random-Date-From-A-Date-Range-In-SQL.htm
Is there any way to get around this?
...
Table data look like this
id val
1 4
2 2
3 1
I want result of subtract valu of val field in one sql statement.
like it should be like 4-2-1 = 1 if order by id asc, 1-2-4 = -5 if order by id desc.
...
What is the MySQL equivalent statement of DBCC INPUTBUFFER(@@SPID), which lists the sql statement for current connection or specified connection?
...
How can I calculate the size of a T-SQL query in bytes transfered across the network?
I know I can approximate it by examining data types of columns (varchars are an interesting twist though), but are there tools that'll give me the number of bytes (including and excluding TCP/IP headers) used up while transferring the query and its res...
I'm trying to restore backups of our production databases to our development server. When I run the following script which has previously worked:
RESTORE DATABASE M2MDATA01 FROM DISK = 'C:\Install\SQLBackup\M2MDATA01.SQLBackup' WITH REPLACE,
MOVE 'M2MDATA01' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\M2...
How would I go about generating a unique receipt number in the following range:
GA00000-GZ99999? I am not allowed to use the 'I' and 'O' letters so GI00000-GI99999 & GO00000-GO99999 would be excluded.
Ideally, I'd like to create this in T-SQL but can also do it in VB.Net. This number will be stored in SQL and I can access it prior to ge...