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