tsql

SQL - Ordering by multiple criteria

I have a table of categories. Each category can either be a root level category (parent is NULL), or have a parent which is a root level category. There can't be more than one level of nesting. I have the following table structure: Is there any way I could use a query which produced the following output: Free Stuff Hardware Movies ...

Updating a table with missing records from another table

Hi, I am running a query that returns records from the LEFT of the EXCEPT that are not on the right query; USE AdventureWorks; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; Lets say there are 6 records returned (there are 6 records in Production.Product table, that are not in Producti...

Execute xp_cmdshell command as specific user

Hello, I would like to run xp_cmdshell (TSQL procedure) in order to mount a network drive and then access remotes mdb files. I am administrator on the MS SQL server and I have allowed xp_cmdshell execution accordingly. However, there is still a problem: When I call xp_cmdshell, the user executing the command is the SQL SysAdmin, i.e...

How can you cancel a SQL Server execution process programmatically

Let's say you have execute the following (long running) process from your code: int processID = DB.Execute(SQL); //some long running sql statement Is there a way to programatically call SQL Server to cancel the process if it is taking too long (kind of like hitting the "Stop" button in QueryAnalyzer)? //cancel the process if it is ta...

How to properly construct SQL subquery in this code?

When I execute the following code, I'm getting results such as: ID column1 column2 34 NULL NULL 34 Org13 Org13 36 NULL NULL 36 NULL Org2 36 Org4 NULL 41 NULL NULL 41 NULL Org5 41 Org3 NULL I want my results to look like: ID column1 column2 34 Org13 Org13 36 Org4 Org2 41 Org3 Org5 I've got two tables: Table1 and Ta...

SQL Server: Find out what row caused the TSQL to fail (SSIS)

SQL Server 2005 Question: I'm working on a data conversion project where I'm taking 80k+ rows and moving them from one table to another. When I run the TSQL, it bombs with various errors having to do with converting types, or whatever. Is there a way to find out what row caused the error? ===================== UPDATE: I'm performing...

SQL - Difference between these Joins?

I should probably know this by now, but what, if any is the difference between the two statements below? The nested join: SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID The more traditional join: SELECT t1.* FROM table1 t1...

Slow exists check when adding a conditional test

I am using this condition as part of my where clause: exists (select * from PRPB PB where PA.mid = PB.mid and (@inpo is null or PB.inpo = @inpo)) order by price. While testing non-null @inpo values, I noticed the query runs much faster when I instead use this condition: exists (select * from PRPB PB where PA.mid = PB.mid and (PB.inpo = ...

tsql- strong passwords

Hi, Using t-sql, how can i find if all SQL Logins have strong passwords on SQL 2000 and 2005 servers? Any help, much appreciated. Regards Manjot ...

SQL to split string and put to lower case?

I have a User_Id column with data DOMAIN\USERID I need it to be DOMAIN\userid Whats the (sql server) sql? Update: Answer from @David McEwing update UserTable set [User_Id] = SUBSTRING( [User_Id], 0, CHARINDEX('\', [User_Id])+1) + lower(SUBSTRING( [User_Id], CHARINDEX('\', [User_Id])+1, len( [User_Id]))) ...

Linq to Entities many to many select query

Hi, I am at a loss with the following query, which is peanuts in plain T-SQL. We have three physical tables: Band (PK=BandId) MusicStyle (PK=MuicStyleId) BandMusicStyle (PK=BandId+MusicStyleId, FK=BandId, MusicStyleId) Now what I'm trying to do is get a list of MusicStyles that are linked to a Band which contains a certain searchs...

Exception Access Violation in SQL

I'm having intermittent trouble with an OPENQUERY statement that pulls data through ODBC from a Progress database into SQL 2005. Most of the time it works, but every couple weeks, one of the many calls using this method bombs with a SQL Stack dump, shown below. We've run exhaustive hardware checks and also DB integrity checks, and have c...

Writing a query to get all records in a group based on an item in the group

Sorry for the unhelpful title, but hopefully I can explain this well enough. Lets say I have three tables, Item ItemKey ItemName Group GroupKey GroupItem GroupKey ItemKey Given an ItemKey, how would I get all records from the item table that belong to a group containing the item key? So if I have Item 1 ItemA 2...

How do I use T-SQL Group By with multiple tables?

I have a table of suppliers, and tables Computers, Cameras, Displays all containing the SupplierID field. I am trying to do a T-SQL that will list all the suppliers, with a count of all rows. I can do them one at a time with: SELECT SupplierID, COUNT(dbo.Computers.ComputerID) as Computers FROM Supplier INNER JOIN Computers ON S...

Unpivot vs. Union queries in T-SQL 2008

Warning: This is a long question The database that I am pulling data from has the table structure like this Table: ClientSales ClientSalesId int identity (1, 1) (PK) ClientId int (FK) TermId int (FK) StudentType1Population int StudentType1Adjustment int StudentType1Sales int StudentType1SalesAdjustment int Student...

Which of two ways of coding an Inner join is faster?

I much prefer to code in t-sql using what is in effect an in-line join, rather than have a long list of the joins at the end of the stored procedure or view. For example, I code: SELECT PKey , Billable, (SELECT LastName FROM Contact.dbo.Contacts WHERE (Pkey = Contacts_PKey)), (SELECT Description FROM Common.dbo.LMa...

Efficiently Handling Multiple Optional Constraints in Where Clause

This is somewhat of a sequel to Slow Exists Check. Alex's suggestion works and successfully avoids code repetition, but I still end up with a second issue. Consider the example below (From AlexKuznetsov). In it, I have two branches to handle 1 contraint. If I had 2 optional constraints, I would end up with 4 branches. Basically, the...

Northwind query exercises/examples

Thus far all I have done with SQL are simple select statements, but now I've also started learning about INNER JOINs, GROUP BY clauses and inner queries. I learned the concepts, but now I’d like to do some exercises using the Northwind database, so that such queries would become second nature. I tried to come up with good examples tha...

SQL Server: Quick way to grant Exec permissions to DB role for many stored procs

Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures. When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role. Here are the steps to apply permissions: select the object that you want to gra...

How do I paramaterise a T-SQL stored procedure that drops a table?

I'm after a simple stored procedure to drop tables. Here's my first attempt: CREATE PROC bsp_susf_DeleteTable (@TableName char) AS IF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName) BEGIN DROP TABLE @TableName END When I parse this in MS Query Analyser I get the following error: Server: Msg 170, Level 15, State 1, Proced...