How to I get the SCHEMA when doing a select on sysobjects? I am modifing a stored procedure called SearchObjectsForText which returns only the Name but I would also like to include the SCHEMA. Right now it is doing something similar to this: SELECT DISTINCT name FROM sysobjects I would like to know what tables need to be joined to ...
Im having a moment, what i want to do is really simple and i think im just looking at the wrong solution. I want to simply return a table that is sorted based on a datestamp of a related table. ie: Table 1: 200 MyStuff OK 201 Other Why 202 Flat So Table 2: 1 200 5/12/2009 MyValue1 2 200 5/11/2009 MyValue2 3 20...
Recently I have been working with a SQL Server database and I was trying to create some triggers for some tables using SQuirreL SQL Client and for some reason I was unable to get rid of the following pesky error message: "Error: 'CREATE TRIGGER' must be the first statement in a query batch. SQLState: S0001 ErrorCode: 111" The query I ...
We have a table called table1 ... (c1 int indentity,c2 datetime not null,c3 varchar(50) not null, c4 varchar(50) not null,c5 int not null,c6 int ,c7 int) on column c1 is primary key(clusterd Index) on column c2 is index_2(Nonclusterd) on column c3 is index_2(Nonclusterd) on column c4 is index_2(Nonclusterd) on column c5 is index_2(...
Is it possible to use TRY CATCH blocks in SQL Selects? For stuff similar to this for example: select order, CONVERT(DATETIME, orderDate) from orders What's the best way of handling this scenario? ...
I am currently messing around with some stuff for an idea for a site - where I pretty much want to enable my users to create "Tables" which holds data and then allow them to query over this data (in a less geeky way than writing up SQL Queries and hopefully easier than using excel). My idea, so far, is to represent this in my database u...
I have a query that looks like SELECT P.Column1, P.Column2, P.Column3, ... ( SELECT A.ColumnX, A.ColumnY, ... FROM dbo.TableReturningFunc1(@StaticParam1, @StaticParam2) AS A WHERE A.Key = P.Key FOR XML AUTO, TYPE ), ( SELECT B.ColumnX, B.ColumnY, ... FROM ...
I have a table Persons with personaldata and so on. There are lots of columns but the once of interest here are: addressindex, lastname and firstname where addressindex is a unique address drilled down to the door of the apartment. So if I have 'like below' two persons with the lastname and one the firstnames are the same they are most l...
table 1 - pid cid 901 101 901 102 901 103 902 102 902 105 903 105 table 2 - cid 101 102 105 I need the Pid from table1 to show ONLY if ALL the CID's match the CID from Table 2. That means in the case of 901 which has CID 101,102 and 103 should NOT show in select statement cause 103 does not exist in table 2. ...
I was reading the Maximum Capacity Specifications for SQL Server and I came across the designation of a wide table. It's different from a standard table in that is can have as many as 30,000 rows as opposed to a normal (narrow) table that is limited to the more familiar 1024 columns. I googled for wide table, but nothing seem to come up ...
I'll describe what I am trying to achieve: I am passing down to a SP an xml with name value pairs that I put into a table variable, let's say @nameValuePairs. I need to retrieve a list of IDs for expressions (a table) with those exact match of name-value pairs (attributes, another table) associated. This is my schema: Expressions ta...
I have a query where I join a table on itself to find mismatches between a parts height or width. The only problem is that because of this join it will return each miss-match twice for each part. I only want to return one row for each miss-match, not two. Here's the table: tblTagGlass JobID varchar UnitCode varchar PartCode varchar Par...
I wrote some T-SQL which will concatenate all my strings in one column together but nvarchar(max) is not enough to store it. Is there another way? The code I'm trying to use is: DECLARE @codes NVARCHAR(max) SET @codes = '' SELECT @codes = @codes + ',' + CONVERT(nvarchar,code) FROM dbo.listing SELECT @codes ...
I have a table with values in one column which have to be splitted by some function, let say F. The function F takes the value and splits it into a table of values - the result is a table. What is the most effective way to apply this function to every value of the source table and have the result table with all values splitted? I kno...
I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ';' . Example CSV FILEcontents: FirstName;LastName;Country;Age Roger;Mouthout;Belgium;55 SQL Person Table Columns: FName,LName,Country ...
Say I have a result set that can contain one or more values, but I always only want one value in the result, and specific values are worth more than others. How would I solve this in sql so I can use it in a sub-query? Example (T-SQL specific, that's the accent I work with): I have a table: tChore(ChoreId int primary key, ChoreDescri...
One category can have many products. I have to build StoredProcedure that returns all categories with some data from Products combined into one field: SP Result: idCategory Name ProductNames ProductQuantities 1 Cat1 Procut1,Product2 24,32 2 Cat2 ProductX,ProductY 0,61 ProductNames and Product...
Lets say I have a table with columns such as: ID Name City State ZIP I need to write a query that will return only one row. This row will include City, State, and ZIP, but I only want a field to have a value if all values in the results set are the same, otherwise I want the field to be null. For example, if every record has the sam...
Hey Now, Is there a 'Max # of result sets that can be displayed' when executing a stored procedure & including the actual execution plan (Ctrl+M)? The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane. Thx, ...
Can anyone tell me if writing a query in the following tsql syntax is either (1) currently -- or going to be soon -- deprecated by MSFT, or (2) in opposition to some best practice of which I'm not aware? SELECT 'CustName' = (SELECT Lastname + ', ' + Firstname FROM Cust WHERE CustID = O.CustID), 'ProdName' = (SELECT ProductName FROM Pro...