Let's say I have two tables, "Parent" and "Child". Parent-to-Child is a many:many relationship, implemented through a standard cross-referencing table.
I want to find all records of Parent that are referenced by ALL members of a given set of Child using SQL (in particular MS SQL Server's T-SQL; 2005 syntax is acceptable).
For example l...
There are three tables, Students, Classes, Student_Class
Is it possible to select students who are not registered in any classes without using NOT IN?
e.g. Select studentname from Students Where studentid NOT IN (Select Distinct studentid From Student_Class)
...
I have seen various methods used when retrieving the value of a primary key identity field after insert.
declare @t table (
id int identity primary key,
somecol datetime default getdate()
)
insert into @t
default values
select SCOPE_IDENTITY() --returns 1
select @@IDENTITY --returns 1
Returning a table of identities following...
How can I ignore an output parameter of a stored procedure. I'm calling the sp from another sp.
e.g.:
DECLARE @param1 integer
EXEC mystoredprocedure @in_param_1, @in_param2_, @param1 OUTPUT, what do I type here to ignore the second output param
I'm using T-SQL (MS SQL 2005).
Thanks for your help.
...
Hi,
I have a large varbinary field in one of my tables, and I would like to download in parts for show a download progress indicator in my application.
How can I split the data sent in a SELECT query?
Thanks
...
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?
(SQL answers preferable over clicking about in the GUI of the management studio.)
Thanks in advance.
...
Hi,
I have stored procedures in SQL Server T-SQL that are called from .NET within a transaction scope.
Within my stored procedure, I am doing some logging to some auditing tables. I insert a row into the auditing table, and then later on in the transaction fill it up with more information by means of an update.
What I am finding, is t...
Any idea if it's possible to create a procedure in another database using T-SQL alone, where the name of the database is not known up front and has to be read from a table? Kind of like this example:
Use [MasterDatabase]
Declare @FirstDatabase nvarchar(100)
Select Top 1 @FirstDatabase=[ChildDatabase] From [ChildDatabases]
Declare @SQL n...
How can you check to see if a user can execute a stored procedure in MS SQL server?
I can see if the user has explicit execute permissions by connecting to the master database and executing:
databasename..sp_helpprotect 'storedProcedureName', 'username'
however if the user is a member of a role that has execute permissions sp_helprot...
I have the following statement
SELECT id, descr from mytable
which returns
1, 'Test1'
4, 'Test4'
6, 'Test6'
8, 'Test8'
22, 'Test22'
Now I want the display to Add a sequential character to the first 4 results...
'A', 1, 'Test1'
'B', 4, 'Test4'
'C', 6, 'Test6'
'D', 8, 'Test8'
'',22, 'Test22'
Thoughts?
Edit: Would prefer a SQL Ser...
I've got a SQL statement in SQL Server 2005 that looks something like this:
SELECT * INTO #TempTable FROM FirstTable WHERE <complex where clause>
What I would really, really like is to have the resulting temp table have an extra field that is essentially an integer field counting from 1 up in the order the where clause returned the re...
How do I create a unique constraint on a varchar field that is case sensitive (SQL Server 2005)?
Currently my constraint looks like this:
alter table MyTable
add constraint UK_MyTable_MyUniqueKey unique nonclustered (MyCol)
When I try to insert the following two values, I get a "Violation of UNIQUE KEY constraint..." error.
insert i...
I have to queries:
select id, name, num from pending
id, name, num
1, first, 1
3, third, 12
select id, name, num from completed
id, name, num
1, first, 100
2, second, 20
I want output to combine these, maybe like a pivot table, using T-SQL.
id, name, pending, completed, total
1, first, 1, 100, 101
2, second, null, 20, 20
...
We have a multitude of databases whose files are stored on their own individual mount points in a drive
(ex. Z:\dbname_db\dbname_db.mdf and Z:\dbname_log\dbname_log.ldf)
What I'm looking for is a way to find the available free space of the mount point.
EXEC xp_cmdshell 'fsutil volume diskfree Z:\dbname_db'
But the service is...
Specifically, I wish to get the date format in a pure (ISO) format:
YYYY-MM-DD HH:mm:ss
I'm looking for a SET command or something that I can use.
I do not wish to rely on the culture setting of the server.
Note: I'm interested in the string format in which dates are returned, not entered.
...
I have 6 months of data, how do I replicate only the most current 3 months of data to the subscribers?
...
Hi,
I need to produce a SQL report showing the number of times a particular event happened in each hourly period during the day. My table has a date/time column on it containing the time the event occurred.
How do I do a count of the number of rows that fall within each each hourly period during the day?
So I need to see output like t...
I have an interesting query to do and am trying to find the best way to do it. Basically I have an absence table in our personnel database this records the staff id and then a start date and end date for the absence. End date being null if not yet entered (not returned). I cannot change the design.
They would like a report by month on n...
I am trying to set every row's CheckColor to Blue in the table tblCheckbook (why the hell do people add tbl to the start of every table, I think I know it's a table).
I'm using this query
UPDATE tblCheckbook
SET CheckColor = Blue
However, Microsoft SQL Server Management Studio Express complains Invalid column name 'Blue'.
T...
I want to implement an atomic transaction like the following:
BEGIN TRAN A
SELECT id
FROM Inventory
WITH (???)
WHERE material_id = 25 AND quantity > 10
/*
Process some things using the inventory record and
eventually write some updates that are dependent on the fact that
that specific inventory record had sufficient quantity (greater ...