Tag: sql-server-2005

calculation of amount for quarter

I have a question, I want merge my table data through date, All information provide in given table If Aggregation =Quaterly My table Table-1 Amount | Date | Aggregation 100 | 2013-01-01 | Quaterly 100 | 2013-02-01 | Quaterly 100 | 2013-03-01 | Quaterly 200 | 2013-04-01 | Quaterly 200 | 2013-05-01 | Quaterly 200 […]

SQL Server 2005 getting Error: 18456, Severity: 14, State: 16. in log

SQL Server 2005 SP4 Standard Edition 32-bit. One to a few times during the work day, seemingly at random, I am getting a log entry of “Login failed for user ‘OurDomain\mfreeman’. [CLIENT: 192.168.0.48]”. This is paired with a log entry of “Error: 18456, Severity: 14, State: 16.” at the same time. This is my own […]

sql server 2005 concatenate nvarchar, results sometimes in strange characters

I’m experiencing a very strange behavior when concatenating two nvarchar(20) columns with ‘ – ‘ into a nvarchar(100), in sql server 2005. Here’s the simplified Update Statement: UPDATE tbl3 SET tbl3.col1 = t1.col1+’ – ‘+t2.col1 FROM tbl1 AS t1 INNER JOIN tbl2 AS t2 ON 1=1 INNER JOIN tbl3 AS t3 ON t3.col2 = t1.col2+t2.col2 […]

An aggregate may not appear in the set list of an UPDATE statement Error with randomized set value

I am trying to execute this SQL script via Management Studio, but I get this error: An aggregate may not appear in the set list of an UPDATE statement. I’ve tried some other statements but I couldn’t find any solution for my randomized set value. Many thanks for helps! UPDATE G SET G.SAAT = DATEADD(d, […]

Linked table insert without cursors

tl;dr Using SQLServer 2005 (the minimum we support), is it possible to copy data from linked tables without using a cursor? Details I have two tables with the following rough structure… dbo.[ParentTable] [ItemId] [UserId] [Details] 1 1 ‘One’ 2 1 ‘Two’ dbo.[ChildTable] [ChildId] [ItemId] [Details] 1 1 ‘One A’ 2 1 ‘One B’ 3 2 […]

How to optimize SQL query which returns millions of records

All the example used below is currently for only one Salesorganization and we may have more different salesOrganization number later in future. I have 6 tables which has millions of records. This tables are populated by execution of SSIS package. select count(*) from tmp_materials –11,02,032 select count(*) from tbl_VendorLogoData –20,41,501 select count(*) from TBL_Image_EDV –4,44,063 […]

SQL Server 2005 migration to new environment SQL Server 2012

I am new here. I was recently handed a project to create an environment with Windows 2012 and SQL Server 2012. The goal is to take the existing databases in our current environment which is SQL Server 2005 and Windows 2003 platform. I have tried looking for specific whitepapers to doing this or checklists that […]

SQL Server database security. Attaching database from one instance to other

I installed sql server 2005 Express in a virtual clean Windows xp machine. On the database engine I created two instances, each one of these I have configured mixed authentication and the first user ‘sa’ password is ‘password1’ and the second ‘password2’. Then in the first instance, I have created a new database with a […]

Issue with Multiple Intersects

My query is as follows – I have a table with 2 columns Name and Mobile Number. Eg. [Name]: A, A, B, C, A, D, D, E, and so on [Mobile Number]: 1,1,2,2,4,3 and so on One Name can have many mobile numbers. I need to find all the mobile numbers which are in common […]

Stored procedure call fails when called from asp.net web app but works when called from SSMS

Background: We recently had a project where we had to plot Institutions on a Google map however using the address to pinpoint the institution took way to long to load. Therefore we decided to store the lat/lng in the database against the institution record. To get this we created a Stored Procedure usp_web_kam_Geocode that calls […]

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