Tag: sql-server-2008-r2

How to find specific Value/Table in SQL 2008 Database

I’m looking for a specific Value in an AutoDesk Vault Database. The Value is: TESTFB_SW and it is a property of an .tif File. I have changed the value and logged this using the SQL Server Profiler: declare @p2 dbo.t_propertyvalue insert into @p2 values(106,N’nvarchar’,N’TESTFB_SW’) declare @p3 dbo.t_value insert into @p3 values(106) exec AddProperties @EntityId=158488,@PropertiesTable=@p2,@pdefsToAssocToEntity=@p3,@overwriteExisting=1 In […]

SSMS 2008 Object Explorer 'Refresh' not working

Using SQL Server 2008 R2 Management Studio, when I make a change to a database object, then click ‘Refresh’ in Object Explorer, the items in Object Explorer are not updated. For example, if I right-click a table and select ‘Design’, then remove a column from the table, when I save my change and click Refresh […]

Replace sub-query result in SQL

I need if sub query result is null then it will replace with ‘-‘. I tried this REPLACE ( string_expression , string_pattern , string_replacement ) REPLACE((SELECT [StandNo] FROM [dbo].[BusStand] where id=b.[ReturnStand]), char(0), ‘-‘) But with no solution because i think it is taking subquery as string_expression. Solution below (SELECT isnull([StandNo],’-‘) FROM [dbo].[BusStand] where id=b.[ReturnStand]) also […]

How to sum cells in a column per id in sql server

I am new to Sql server. the sum(datediff(x,y,z)) is giving the sum of both a1 and a2 whereas I want the sum separate for a1 and a2. I dont know how to group it per id. I know that sum will return the sum of both but I want to know how can I separate […]

Optimal passing of optional parameters into a query

I have a parameterized query with optional parameters. Multiple tables are joined. A part of the WHERE clause looks like this: and ((x.a = @arg1) OR (@arg1 IS NULL)) and ((y.b = @arg2) OR (@arg2 IS NULL)) and ((z.c = @arg3) OR (@arg3 IS NULL)) So, the idea is: A parameter can either be used […]

Select list t-sql

I have this code: select top 3 w.firstName, w.lastName, w.fbId, w.idNumber, w.address, w.phoneNumber, q.userId, c.codeVal, q.bidedItem, SUM(q.bidCount) as sumOfBids, sum(w.codesLeft + q.bidCount) as allCode from geopl_kubetiAuct.firstWeekBids as q join geopl_kubetiAuct.Users w on q.userId = w.Id join geopl_kubetiAuct.Code c on c.userId = q.userId where q.bidedItem = ‘GalaxyTabs’ group by w.firstName, w.lastName, w.fbId, w.idNumber, w.address, w.phoneNumber, q.bidedItem, […]

Running counts of records and sum of max() records within date range based specified intervals in t-sql

Sample data: (assume year_month_record is the first day of the month and is datetime data type) location item year_month_record type visits1 visits2 ABC111 11JF445553 2014-01 sales 3 5 ABC111 11JF445553 2014-02 sales 3 6 ABC111 11JF445553 2014-03 sales 2 8 ABC111 11JF445553 2014-04 sales 2 4 ABC111 22WZ777814 2014-02 sales 3 5 ABC111 55RR342013 2014-01 […]

Likelihood of query volume to cause deadlocks and CPU overhead

We are having 2 problems with our database right now: deadlocks maxing out DB CPU The most egregious characteristic I can see is that we are doing very simple tasks with a lot of queries/DMLs. Capturing a 1 or 2 strings now occupies 15 – 20 queries/DMLs. Capturing small-ish surveys with answers for 3 or […]

SQL why sp is slower than query

It may look simple question but i really need help. I have been trying this for two days. I am passing userid as parameter to stored procedure and using that userid i am retrieving companyid to use it in another query. CREATE PROC spXXXXXXX @UserID INT AS BEGIN DECLARE @CompanyID INT SET @CompanyID =(SELECT CompanyID […]

SQL Server 2012: Dynamically identifying the user name who is executing a Stored Procedure

This question already has an answer here: T-SQL: SUSER_SNAME vs SUSER_NAME? 1 answer

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