Sql Server Value Method Returning only one value not multiple

I am trying to return multiple element values into rows and I am running into the below issue of it not returning all instances in rows, I have isolated the issue down to a singleton problem. I have referenced the below links: http://www.jasonstrate.com/2010/11/xquery-for-the-non-expert-value/ http://msdn.microsoft.com/en-us/library/ms175894.aspx I have tried these: ,c.value(‘(Tags/Tag/text())[1]’ , ‘NVARCHAR(20)’) AS Tag And ,c.value(‘(//Tags/Tag)[1]’ […]

Selecting a value from XML data type

How to write a SQL Server query that selects from an xml data type and selects attribute in an element that is matched based on other attribute. Imagine the following example : create table dbo.Configuration(Configuration xml not null); insert into dbo.Configuration(Configuration) values(convert(xml, ‘<?xml version=”1.0″ standalone=”yes”?> <Configuration> <DoSRequestAnalysis> <Windows> <Window Name=”Smallest” Duration=”15″> <ThresholdsToRemoveRequests NoofRequests=”25″ /> </Window> […]

A trigger to update one column value to equal the pkid of the record

I need to write a trigger that will set the value in column 2 = to the value in column 1 after a record has been created. This is what I have so far: create trigger update_docindex2_to_docid ON dbo.TABLENAME after insert AS BEGIN set DOCINDEX2 = DOCID END; I answered my own question one I […]

How to formulate a TSQL CTE expression for total counts?

I have a table with raw data that I want to return distinct people_id counts from. How can I do this in a CTE if the counts have different groupings? Here is what I have so far: ;with cte as (select program_modifier_id, program_modifier, people_id, group_profile_id, current_status, license_number, is_managing_office, program_info, program_name, program_code, group_profile_type_id From #enrollments en […]

SQL Server – Composite key creation

I’m trying to create a composite primary key like this: CREATE TABLE TableA (Column1 nvarchar(50) NOT NULL, Column2 nvarchar(3) NOT NULL, Column3 nvarchar(50) NOT NULL) ALTER TABLE TableA ADD CONSTRAINT PK_AuxGroupData PRIMARY KEY CLUSTERED (Column1 , Column2) By some reason, the second query is generating the following exception: System.Data.SqlServerCe.SqlCeException occurred Message=The constraint specified is not […]

Fetch data from a table using range/condition from columns of another table

I have a table say “Table1” which have tons of data stored under a column name “Value”, i have another table say “Table2” which have columns “from” and “to”. I want to perform a “between” operation on column “value” of “Table1” and operands will be from “from” and “to” columns of “Table2”. For Example: Table1 […]

Use Invoke-SQLCmd and return number of rows deleted/affected

I have a very simple powershell command, where $query is just “delete from mytable” Invoke-sqlcmd -ServerInstance $databaseserver -Username $databaseuser -Password $databasepassword -Database $databasename -Query $query| Out-File -filePath $output However, this does NOT output the number of rows affected? The equivalent in SQLCMD does output the no rows affected: & sqlcmd -S . -U $databaseuser -P […]

create a file system group pointing at a SAN

I am working with creating a FileTable in sql server 2012. I want to store the files on a SAN (Storage Area Network). I have mapped it to a drive on sql server. To use the FileTable, I need to set up a file system group. I am using the following script but failing to […]

I need to get a count from a Declare Statement with a Inner Join

Declare @trans table(eTransid varchar(30) Not NULL) INSERT INTO @trans SELECT DISTINCT eTransID FROM csi_eRecording.dbo.eRecordAudit WITH(NOLOCK) WHERE EventMessage LIKE ‘Recorded file cannot be found%’ ORDER BY eTransID SELECT a.* FROM csi_eRecording.dbo.eRecordTrans a WITH(NOLOCK) INNER JOIN @trans b ON a.eTransID=b.eTransID ORDER BY eTransID

Select query with multiple like conditions in Sql

All, I have a view from which some filtered data is to be fetched by sql query View structure: BridgeId int Name varchar DisplayName varchar There is a search text box in which user can enter any one of the values for filtering. due to project old framework i have to query from c# itself. […]

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