Move Back Up XML Using Nodes

I'm using the Nodes method to shred some XML data into a table, and I'm just missing one part. I need a particular XML element, but its back up (pardon my poor syntax, new to dealing with XML) the XML from where I'm shredding. I've posted, what I hop...
more »

2017-04-17 23:04 (1) Answers

Querying data from two databases in C#

I currently have a query where I am getting data from 2 different databases which is getting appended to one list called "accountbuys". My first table has three data entry(3 accounts which want to buy stocks The next table with 17 data points(17 st...
more »

2017-04-17 21:04 (2) Answers

How to UNION multiple databases as one source

I have 40+ databases (linked servers) in my SSMS connection, each has the same schema, tables, columns but with unique data for each individual location. I'm looking for the best way to UNION them all together into a VIEW or Alias that would allow me...
more »

2017-04-17 17:04 (1) Answers

SSIS: Truncate Excel Destination

I am creating a SSIS package that imporr data from a SQL Server Source to an Excel Destination. How can one truncate spreadsheet before run? I tried the following way (using Execute SQL Task with no success. ...
more »

2017-04-17 15:04 (1) Answers

Issues in creating dynamic SQL query

I was trying to create a dynamic SQL query, But unfortunately it shows an error: Msg 245, Level 16, State 1, Line 14 Conversion failed when converting the varchar value 'select count (s.ID), count (distinct (s.ID)) from SimulationVisit SV inner...
more »

2017-04-17 11:04 (5) Answers

Detecting Unicode Text in SQL Server

I am storing bodies of text in SQL Server. Some bodies of text contain Unicode characters that will be lost when storing in a VARCHAR column within SQL Server. As only a small portion of text bodies stored will require a NVARCHAR column, I have dec...
more »

2017-04-16 22:04 (1) Answers

Detect the cause of SQL Server update lock

Problem: A .NET application during business transaction executes a query like UPDATE Order SET Description = 'some new description` WHERE OrderId = @p1 AND RowVersion = @p2 This query hangs until timeout (several minutes) and then I get an ex...
more »

2017-04-16 08:04 (1) Answers

JSON_MODIFY Remove Array Objects

How do you remove an object from a VARCHAR(MAX) JSON array? Setup DECLARE @Json VARCHAR(MAX) = JSON_QUERY('[{"u":"user1","i":"item1"},{"u":"user2","i":"item2"}]') SELECT @Json DECLARE @NewJson VARCHAR(MAX) = JSON_QUERY('{"u":"user3","i":"item3"}'...
more »

2017-04-15 22:04 (1) Answers

trying to retrieve info from dtsx xml file

I'm trying to pull specific values out of the executable objects in dtsx. I want things like SQLStatementSource, ObjectName, and Connection. I can't seem to do it. I'm using XMLDocument and GetElementsByTagName. Here is my whole code, With DTS:O...
more »

2017-04-15 21:04 (1) Answers

Connecting Django with MSSQL server

I'm trying to connect my Django app to SQL Server 2016. I've tried using django-pyodbc but it doesn't support Django 1.11. Instead I installed django-mssql 1.8. When I try to run the application I get this error. TypeError was unhandled by user code...
more »

2017-04-15 21:04 (0) Answers

loading xml file to stored procedure

I am trying to insert data from Xml file using a stored procedure, stored procedure as below : CREATE PROCEDURE xmlreadtest @xmldoc xml AS BEGIN INSERT INTO Page (KeyId) SELECT [Key].value('@Id[1]', 'VARCHAR (100)') FROM @xmldoc.node...
more »

2017-04-15 19:04 (1) Answers

SYSDATETIME going backwards

First up, I have solved the actual problem that I had, by moving to using a datetime2, and DATEDIFFing at the MICROSECOND scale. But I'm still surprised by the behaviour I was seeing, so .... I'm trying to diagnose a perf issue with some SQL, and ...
more »

2017-04-15 13:04 (1) Answers

SQL Server Agent configuration

Could anybody assist me on how could I possibly configure the SQL Server Agent for my local system (laptop)? It doesn't appear under SSMS Object Explorer. Been to the SQL Server Config Manager > SQL Server Services > SQL Server Agent > Properties.....
more »

2017-04-15 09:04 (1) Answers

Parsing file name in SSIS

I have a file name SandersCoffeNetwork201604211107. I would like create a two new column in SSIS using derived column. However I need an expression that would separate the date from the file name First column FileFromDate 2016...
more »

2017-04-14 23:04 (1) Answers

From submit long running sql query

I have a web application that needs to submit a long running sql stored procedure with parameters. It could run up to 30 minutes. How can I submit to run on the sql server as a job so the web app can continue on with something else? Is it pos...
more »

2017-04-14 14:04 (0) Answers

LEFT JOIN Causes Duplicate Result

I've put together a simplified version of the problem here. Scenario My application has Users, Files, and Folders. A user can create private files that only he can see and shared files that all users can see. A user can create private folders that...
more »

2017-04-14 04:04 (9) Answers

Setting an SSIS Variable from JAMS Scheduler

Executing a file system SSIS package through JAMS scheduler. The following code works fine (Execution method of "SSIS"): /f "C:\Path\To\File.dtsx" I'd like to set the value of one of the variables in the package with a parameter on the job. I've s...
more »

2017-04-14 00:04 (1) Answers

Where does a Remote SSIS package actually run

We have a legacy Sql Agent job that runs on Server A. The job Step points to a SSIS package on Server B. In which environment does that package actually run? We assume that it executes in the environment of the agent that calls it, but because of ...
more »

2017-04-13 18:04 (1) Answers

Divide count by count(*) in SQL Server

Here is my query : SELECT COUNT(*) AS total, COUNT(CASE WHEN IS NULL THEN 1 END) AS nb_null, COUNT(CASE WHEN IS NOT NULL THEN 1 END) AS nb_not_null FROM table t Is it possible to divide a field by an alias? : SELECT ...
more »

2017-04-13 17:04 (4) Answers

Form xml element from SQL Server table

I want to convert the sql table records into below xml format. Can you please give your suggestions in it. SQL Server table: id name ------------- 1 Arun 2 Raghavan I want to form the xml elements from the above table like the below format i...
more »

2017-04-13 15:04 (3) Answers