tsql

Select Parent Record With All Children in SQL

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

Selecting records from a many-to-many design

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

T-SQL: @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

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

Is it possible to ignore an output param of a stored procedure?

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

Split a varbinary in a SELECT

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

How can I list all foreign keys referencing a given table in SQL Server 2005?

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

How to execute inserts and updates outside a transaction in T-SQL

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

Creating stored procedure in another database

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

MS SQL Server: Check to see if a user can execute a stored procedure

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

TSQL: Howto add a char to a select statement.

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

Can select into add a new field to the resulting table?

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

T-SQL: How do I create a unique key that is case sensitive?

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

Merging results in T-SQL statement

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

Finding available drive space of databases stored on a LUN

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

How do I control the format in which MS-SQL Server returns datetime values?

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

SQL Server - Transactional Replication (partial data only)

I have 6 months of data, how do I replicate only the most current 3 months of data to the subscribers? ...

How do I write some SQL that will return results showing the number of rows that occured in each hourly period?

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

T-SQL absence by month from start date end date

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

MS SQL update Query, gotta be something simple

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

Which lock hints should I use (T-SQL)?

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