XML Value buried inside the node

I'm brand new to XML, but not SQL. I have data structured by our app vendor as follows that I'm trying to load up into a table: <windowsets> <windows> <question> <id Value="81b25d-9385-sk3" /> <displayna...
more »

2017-02-14 16:02 (2) Answers

Remove duplicate data from query results

I have the following query: select C.ClientID, C.FirstName + ' ' + C.LastName as ClientName, CAST(V.StartDate as date) as VisitDate, count(*) as 'Number of Visits' from Visit V Inner Join Client C on V.ClientID = C.Client...
more »

2017-02-14 13:02 (4) Answers

Dapper and user defined database types

Let's say I have a user defined data type in the database: MySuperType which as two bigint properties in it (Id + Code) And I have a stored procedure which takes in those parameters: @MySuperType, @Price, @dateModified, etc... Now in my code, I ...
more »

2017-02-14 09:02 (1) Answers

How to simplify following SQL query?

more »

2017-02-14 09:02 (2) Answers

Select sum with other table in SQL

How do I select sum with other table if I have data like below: Table Member MemberID Name DateJoin M0001 John 01/01/2015 M0002 Willy 03/20/2016 M0003 Teddy 02/01/2017 etc.... Table Transaction MemberID Tr...
more »

2017-02-14 06:02 (3) Answers

SQL Server Missing Backups

I'm trying to get a list of missing full backups, but I need this list to show me which user performed the latest backup. The problem is, I don't know how to filter that, since what I'm querying is returning all the last backups for each user. Examp...
more »

2017-02-14 03:02 (1) Answers

storing feet/inches in DB

I have many different strings with feet/inches like this: 10 feet and 8 1/4 inches 7 feet and 4 3/8 inches I need to store it to DB (I use MS SQL) How to do it the best way? I see the following approaches: store separately. One field for feet, ...
more »

2017-02-13 18:02 (2) Answers

Check for an empty csv file in HTTP location

I am trying to follow Determine if file is empty (SSIS) to see if the file is empty at the HTTP location. I cant download it to begin with as the process is stuck at source and wont let my package finish. All I want to do is to query to source file, ...
more »

2017-02-13 17:02 (2) Answers

SQL Server Merge query - duplicates?

I have 2 table variables First table has 1, 3, 5 Second table has 2, 4, 5 However when I run the following query : DECLARE @t1 TABLE (a int) DECLARE @t2 TABLE (b int) INSERT INTO @t1 (a) VALUES ( 1 ),(3),(5) INSERT INTO @t2 (b) VALUES ...
more »

2017-02-13 16:02 (2) Answers

Storing emojis in SQL Server table

So I have been trying to save emojis received via a POST HTTP request from an Android app into an SQL Server table using Laravel. Though special characters work fine, emojis give syntax error as following- SQLSTATE[HY000]: General error: 20018 In...
more »

2017-02-13 13:02 (1) Answers

Optimize LINQ query that runs fast in Sql server?

I want to calculate the rows of a related table: MainTable tbl = tblInfo(id); var count = tbl.Related_Huge_Table_Data.Count(); The problem is: this takes too long (about 20 seconds) to execute, although when I run this query in Sql Server it execu...
more »

2017-02-12 12:02 (4) Answers

Is this approach safe to use?

I'm fairly new to working with SQL Server and ASP.NET so I'm worried about SQL injection or other threats. I have a FormView referencing a SqlDataSource where the FormView has code-behind logic preventing unauthorized users from making changes to the...
more »

2017-02-11 17:02 (1) Answers

SQL JOIN Returning no values

I am working with 3 tables with the following Names and columns: *Table 1* **Users** UserID UserName UserTypeNumber 1 John 1N 2 Mary 1N 3 Doe 1N 4 Sullivan 2N 5 ...
more »

2017-02-11 15:02 (2) Answers

How to use Json Parser in CLR Procedure?

I am getting CRAZY trying to use a Json Serializer/Deserializer in my Class Library and import my Assembly in SQL Server. I am working with a WebAPI that response Json string , and I want to create CLR Sql Procedure that call that API and use the ap...
more »

2017-02-11 13:02 (2) Answers

Including a forward slash in Kerberos Principle

I am attempting to connect to a MSSQL database using pyodbc and Kerberos on a Mac. It is entirely possible this is not my issue at all but I suspect that I need to include a forward slash in the username. This is why I think this... I am able to suc...
more »

2017-02-11 04:02 (0) Answers