Tag: tsql

create sql hierarchyid siblings

I have a database table to which I have just added a hierarchy column. The only other relevant column is the ID column (primary key). The entry with ID = 1 is my root (set to HierarchyID::GetRoot()). I can create a child in the hierarchy just fine, however I cannot seem to figure out a […]

How can I SELECT record from database WHERE a number is CLOSEST to one I supply?

I am no database expert and really have no idea even how to approach this. I’m sure it is simple for someone who knows TSQL and would welcome some insights please. I have a database with postcodes and Eastings and Northings. Example: Postcode Easting Northing ————————— HR74DW 365601 254668 B129DB 407863 284797 I would like […]

Trigger and stored procedure security in SQL Server

I have a database (SQL Server) that is being used by 20 users, all members of the same security role. The role enables them to insert, delete and update to Table1, but they have no permissions for Table2. Table1 has a trigger that fires a stored procedure, Table2_Refresh, that truncates Table2 and rebuilds it from […]

Invalid column in stored procedure

I have a query in a stored procedure, it works fine. now I want to add a column the it show error. My stored procedure code is: ALTER PROCEDURE dbo.test @SDate DATETIME =Null , @EDate DATETIME=Null ,@period int=Null AS BEGIN SET NOCOUNT ON; if @period = 1 Begin SELECT t.TotalQuote , t.QuoteAmount ,t.avgProbQ , t2.TotalOrders […]

Insert from single table into multiple tables, invalid column name error

I am trying to do the following but getting an “Invalid Column Name {column}” error. Can someone please help me see the error of my ways? We recently split a transaction table into 2 tables, one containing the often updated report column names and the other containing the unchanging transactions. This leave me trying to […]

Within the same group find and exclude records that have the same parent ID for certain types

I have a table like following: GroupID ParentID Type 1 ABC IND 1 ABC IND 1 CDE ORD 1 EFG STD 2 ZZZ IND 2 ZZZ IND 2 ZZZ IND 3 YYY COR 3 YYY COR I need to exclude those records that are in the same group, having the same parent ID and the […]

creating a TSQL CTE to auto increment with a varchar(max)

I have the following CTE: ;WITH combo (id, [program_name]) AS ( SELECT 1 , CAST(” AS VARCHAR(MAX)) UNION ALL SELECT cte.id + 1 ,(cte.[program_name] + pl.[program_name] + ‘; ‘) FROM ( SELECT RowNum = ROW_NUMBER() OVER (ORDER BY people_id) –people_id ,p.[program_name] FROM event_log_Rv E JOIN PROgrams_view p on p.program_info_id = e.program_providing_service where people_id = ‘DFA3AFE5-F681-4B1B-89F0-31D04FA6BF7D’ […]

How do I merge data from two tables into one using Sql Server 2008?

I’m using a SQL merge and trying to merge from two tables into a single table. Basically, there are three tables named: t1,t2,t3. What I’m attempting to do is to get the t2 table data and t3 table data into the t1 table using merge. Here is the code: MERGE daily_so_invoice AS target1 USING temp_invoice […]

SQL Query to return Remote Access Configuration

I’m trying to develop a script that will query a SQL DB and its instances to see if remote access is enabled. I can find lots of information on how to manually do this through the SQL Management Console and commands on how to alter the access, but my search is coming up empty on […]

having a count column from a subquery that's querying the same table

Lets say i have the table: accounts: id, id2, id3, custId, option1, option2, type and this same table has ALL accounts with some being “parent” accounts (which can be “solo” accounts with no children) and some being “children” accounts (bad design i know, but that’s what I’m working with). The distinct/composite key for each account […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.