Code Answers

Sql – Timeout error in VB.NET while running a sql script

I was wondering if someone can help me figure why I am getting this error from a simple stored procedure that I created in SQL Server 2008 R2. I am trying to obtain data and insert it into a temporary table. if it is a timeout issue, how can I increase the timeout length to 420 seconds. to my understanding the timeout is good for only 30 sec...

Sql – The multi-part identifier could not be bound

Possible Duplicate: The multi-part identifier could not be bound This is my query that doesn't want to work. It says: The multi-part identifier "dbo.RunSequenceBatch.RunSequenceBatchName" could not be bound. Could you please advise? Thanks in advance!!! 🙂 SELECT '30 May' AS [Date], [RunSeq].[RunSequenceBatchName] AS [Batch Job Name] , RunS...

Sql update multiple rows with subselect

I am trying to copy the corresponding graduation_date values from the graduation_term table into the rows in the user_education_mba_school table that have the matching graduation_term_id. Here is my nonworking query TABLE DEFS **user_education_mba_school school_id graduation_date graduation_term_id **graduation_term graduation_term_id...

Sql – Alias column name for Use in CASE Statement

I have SQL query as below: SELECT a.ID as AID, a.Amt as AAmt FROM (SELECT ID, CASE WHEN Col1 = 0 THEN SUM (Col2 + Col3) ELSE 0 END AS Amt FROM table1 GROUP BY ID, Amt) AS a I get an error: Invalid column name 'Amt'. (note: t...

Sql – String or binary data would be truncated

First to clarify, I have already done a search and found a few identical questions, but the answers did not solve my problem. I have this code: SET IDENTITY_INSERT [STUDY] ON INSERT INTO [STUDY] ([STUDY_ID],[PARENT_ID],[GROUP_ID],[WORKFLOW_NODE_ID],[STUDY_TEMPLATE_ID] ,[INSPECTION_PLAN_ID],[NAME],[DESCRIPTION],[STATUS],[O...

SQL Server OFFSET equivalent

I am using SQL Server 2008 Enterprise on Windows Server 2008. I want to select result from top 11 to top 20 (e.g. I am only interested in the 11th to the 20th result). Any ideas how to write this query in tsql efficiently? thanks in advance, George Solution to answer: Unfortunately SQL Server does not offer anything similar to MySQL's OF...

SQL Server sp_ExecuteSQL and Execution Plans

I have a query which is super fast in SQL Server Management Studio and super slow when run under sp_ExecuteSQL. Is this to do with caching of execution plans not happening when run under sp_ExecuteSQL? Solution to answer: No. You can see both execution plans and compare them using the following query. SELECT usecounts, cacheobjtype, objt...

SQL “Incorrect syntax near the keyword ‘from’.”

I'm new to SQL and visual studio etc. but I've changed something that isn't allowing me to login to my application. Whenever I press the login button I get this error Incorrect syntax near the keyword 'from' Here is where the source may be; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web....

SQL, Error converting data type varchar to numeric

SELECT LIFNR, SUM(CASE WHEN UPPER([WAERS])='USD' THEN ROUND(CONVERT(numeric(10,2),DMBTR)* (-1),2) WHEN UPPER(WAERS)='USD' THEN ROUND(CONVERT(numeric(10,2),DMBTR),2) ELSE ROUND(CONVERT(numeric(10,2),DMBTR),2) END) AS 'AMOUNT' FROM tbl_BSAK GROUP BY LIFNR; Hi, everyone. I just wrote a piece of code...

Sql – Arithmetic overflow error converting numeric to data type numeric

I am facing an error on my SQL script: Arithmetic overflow error converting numeric to data type numeric select x.MemberName,x.DOB,x.FilePath,x.Medication,x.NDC,x.Directions,x.Name,x.Strength,x.GenericName,x.QtyOrdered,x.DaysSupply,x.DateFilled, CASE WHEN x.test = 0 THEN 'N/A' WHEN compliance > 100.0 THEN '100.0' ELSE CONVERT(...

Sql – Conversion failed when converting the nvarchar value ” to data type int

While this may sound like a beginner 101 problem I think it is a bit more complicated. I have two instances of SQL server, one is a log-shipped read/only standby copy of the master database that is used for reporting purposes. They are both 64-bit SQL 2005, SP3. The LogShipped Instance is: 9.00.4035.00 (Standard Edition) The Original Instance i...

Sql – Arithmetic overflow error converting numeric to data type varchar

So, here's my query: SELECT '$' + CONVERT(VARCHAR (6), Cast(Avg(TotalPrice) AS NUMERIC (6, 2))) AS 'Average Price', '$' + CONVERT(VARCHAR (6), Cast(Min(TotalPrice) AS NUMERIC (6, 2))) AS 'Minimum Price', '$' + CONVERT(VARCHAR (6), Cast(Max(TotalPrice) AS NUMERIC (6, 2))) AS 'Maximum Price' FROM Invoice; T...