Tag: sql-server-2008-r2

Stored Procedure sometimes timeouts when calling via RPC, but always OK when calling in SQL

We have a very strange problem: We have a stored procedure which returns a set of data. This procedure does a rather complex join and aggregation, so it takes 700 Milliseconds to execute. When called directly in SQL Studio, the procedure ALWAYS returns correcty and always takes about 700 ms of time. However, when called […]

query execution performance issues with a NOT IN in where clause

I have a query such SELECT SOME_FILEDS FROM MY_VIEW MY_VIEW is a view retrieving data from other views, it takes 45 minutes to execute a query from MY_VIEW on a production server. By trying to invesetigate the problem i realized that one sub-view is slow. I know that creating many views is not the best […]

How to retrieve SQL Server Check Constraint Dependency Columns

If I create the following table create table test( id1 int, id2 int, id3 int constraint CK1 check (id3 > 2), constraint CK2 check (id1 > id2), ) I can find the dependencies of CK1 by querying select * from sys.check_constraints. The parent_column_id will return the correct answer of 3. The However, CK2 is a […]

SQL Server Windows Login – Same Name Different User (SID)

I’m having a problem with an windows login to sql 2008 r2 server. A user was created in active directory lets call him “mydomain\jon.doe” and an windows login was created on the SQL server. Because of an problem with exchange this AD account was deleted and a new one created still called “mydomain\jon.doe” I ran […]

How do I insert rows if their values appear in a list but are not in the table and delete them if they do not appear in the list but are in the table?

Recently, I was put in charge of cleaning up a SQL Server 2008 database and code for website, and am having trouble coming up with an elegant solution to the following problem. On the website, there are users that have different roles, and each role has a number of permissions. The admin on the website […]

sql query to find the Items with the highest difference

I have my database table ABC as shown below : ItemId Month Year Sales 1 1 2013 333 1 2 2013 454 2 1 2013 434 and so on . I would like to write a query to find the top 3 items that have had the highest increase in sales from last month to […]

Deleting rows from the main table using a column of Temp table?

I Have to delete stackID’s which are there in ##BeDel from Main table by using @flow. #BeDel StackID 45 56 48 56 49 DECLARE @flow int @flow=(SELECT FLOWID FROM MainTable WHERE FLOWNAME=’AAA’) MainTable StackID VALUE FLOWID FLOWNAME 67 34 1 AAA 45 56 1 AAA 56 22 1 AAA 34 56 1 ZZZ I have […]

Display data in a Table

Goal: Display the data of xml in a customized table. The requested table list will be user firstname lastname hour projectname sex ————————————————————————- userTime sara brown 20 null null userProject jessica black null Melissa null userProject Jim west null Sakura null userSex robert lake null null male etc…. Problem: In the hiearchy I have three […]

time format and string format

I’m using SQL server to form file names to Ms Word documents. The format is like this clientnumber_date_time_clientname.doc. Problem is Ms Word doesn’t accept 10:19:23 time format as file name. How to change it into 10-19-23 format? In some cases clent name is something like this: the “Coca Cola” company. Apparently, Ws Word doesn’t accept […]

Running total of records based on date ranges in t-sql

Sample data: groupID CustomerID CustomerAddr work_date work_order CA123 ABC12345 123 MAIN ST 2/1/2012 WORKNEW CA123 ABC12345 123 MAIN ST 10/9/2012 ZZZ888 CA123 ABC12345 123 MAIN ST 3/9/2013 ZZZ131 WA999 ZZZ99909 451 EAST ST 1/13/2013 SY1234 WA999 ZZZ99909 451 EAST ST 4/15/2013 WORKOTHER WA999 ZZZ99909 451 EAST ST 5/17/2013 SY1244 WA999 ZZZ99909 451 EAST ST 12/8/2013 […]

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