tsql

TSQL foreign keys on views?

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...

SQL Insert multiple rows using stored procedure and xml parameter?

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 ...

Linq query with multiple Sums, Groupings and Joins

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...

SQL Select Condition Question

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...

How do you generate INSERT stored procedures for all tables in a SQL2008 database?

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? ...

generate script of content of one table as a .sql file(via code)

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? ...

How can I rewrite this statement?

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...

How do I select rows in table (A) sharing the same foreign key (itemId) where multiple rows in table have the values in table B

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...

is it invalid to set a reference to null or 0?

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 ...

Is there a way to get the fields names of a table created in a function / stored procedure?

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...

SQL Server: How do I get the value for the row I inserted?

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...

SQL: How do I use parameter for TOP like in SELECT TOP @amount?

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 ...

SQL Server Query Optimization: Where (Col=@Col or @Col=Null)

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....

SQL Server getdate() to a string like "2009-12-20"

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...

TSQL: Why cannot use newid() inside a scalar-valued function

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? ...

Subtract all values from data

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 equivalent statement of DBCC INPUTBUFFER(@@SPID)(which give sql statement for current connection otr specified connection) in MYSQL?

What is the MySQL equivalent statement of DBCC INPUTBUFFER(@@SPID), which lists the sql statement for current connection or specified connection? ...

How do I measure the cost of a T-SQL query on the wire?

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...

Trying to restore via t-sql script. Exclusive access could not be obtained because the database is in use.

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...

Generate a receipt number in this range

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...