Large Database Query Affecting Entire System

We have a Windows Application (written in .Net) that supports about 500+ users. There is a specific function on this applications that’ll bring back about 40k records (on a MS SQL Server) that gets run a handful times per day. However, when this large query is run other users of the system seem to be […]

How to read/export XML file which is output from SQL server in JAVA

I have a Query, which I execute in SQL server 2005/2008, gives me output in XML format. Here is the sample query to generate xml output : SELECT (SELECT ‘White’ AS Color1, ‘Blue’ AS Color2, ‘Black’ AS Color3, ‘Light’ AS ‘Color4/@Special’, ‘Green’ AS Color4, ‘Red’ AS Color5 FOR XML PATH(‘Colors’),TYPE), (SELECT ‘Apple’ AS Fruits1, ‘Pineapple’ […]

Joining 2 SQL Servers

I am trying to search 2 servers (A and B). Server A has database PROD_A and Server B has PROD_B. PROD_A has table1 and PROB_B has table2. How can I merge 2 tables from 2 different SQL Servers? Already done setting up link servers but I am having issues with query syntax. Thanks.

Wrapping system procedure in View or Stored Procedure

I am trying to call master..xp_fixeddrives from an application using .NET Entity Framework, so I need the results to be parsable. I am looking to call master..xp_fixeddrives, but for EF, it must be in a view or Stored Procedure. I can’t figure out the syntax to create this. I tried defining a view, and tried […]

select default value when record not found in where

Given the following query: SELECT COALESCE(A, 0), COALESCE(B, 0) FROM TABLE WHERE Id IN (0, 2, 0); How would it be possible to return a default value when no record is found based on the where clause? The following is the expected result: 0, 0 1, 1 0, 0 Note that when the Id is […]

IIS Hosting and SQL Server 2008 R2 not available online

I have succeeded in hosting my applications locally but when i try to view it through a network outside it fails. Same goes for my SQL Server, I am able to connect locally. I have tried to open my port but it keeps failing, Is the port the cause of the problem? Server Manager is […]

Add up all the values in a column

This question already has an answer here: SQL – Add up all row-values of one column in a singletable 3 answers

Changing Column Datatype After Data Insertion

I’m currently trying to localize a database, and my strategy involves taking all localizable strings out of my various tables, and putting them into another table containing a StringID, a CultureID and the LocalizedString, which is then referenced within the original table by the StringID. The problem is that I need to change the datatype […]

How to calculate the date difference between rows with special first row rule

I have multiple applications each with unique application ID’s, Each application has logs which are stored in a table. I want to know how to calculate the date difference eg: AppID Start Loggedon change A1 08/07/2010 08/09/2010 Xchange A1 08/07/2010 08/20/2010 Ychange A1 08/07/2010 08/30/2010 Zchange A2 07/07/2010 07/13/2010 Ychange A3 09/07/2010 09/09/2010 Xchange So […]

Created Primary Key on SQL Server not Identified

CREATE TABLE sampProduct ( Product_ID VARCHAR(15) NOT NULL, Supplier_ID INT NOT NULL, Category_ID INT NOT NULL, Unit_Price DECIMAL(10, 2) PRIMARY KEY(Product_ID) ) CREATE TABLE sampMachine ( M_Product_ID VARCHAR(15) NOT NULL, Serial_No VARCHAR(15) NOT NULL, Machine_Model VARCHAR(20), PRIMARY KEY(M_Product_ID, Serial_No), FOREIGN KEY(M_Product_ID) REFERENCES sampProduct(Product_ID) ) CREATE TABLE sampService ( Service_ID VARCHAR(15) NOT NULL, Serial_No VARCHAR(15) NOT […]

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