tsql

ORDER BY with a CASE statement for column with alias

I need a stored procedure which will allow me to return sorted results based on two input parameters: @sortColumnName and @sortDirection. I wrote the following stored procedure, but when I run it, I am getting this error: "Invalid column name 'LastPayCheckDate'." SELECT Name, SUM(Pay), MAX(PayCheckDate) as LastPayCheckDate FROM Employee...

Linked Server Query Runs But Doesn't Finish?

June 29, 2010 - I had an un-committed action from a previous delete statement. I committed the action and I got another error about conflicting primary id's. I can fix that. So morale of the story, commit your actions. Original Question - I'm trying to run this query: with spd_data as ( select * from openquery(IRPROD,'select * from ...

Why isn't my parameterized query working?

I have a kinda complex query, basically I search the database in most fields for a string or strings. If it's multiple strings, the database field must match all parts of the strings. This is the base sql that the query is built on: SELECT wo.ID, {columns} FROM tblWorkOrder wo LEFT JOIN tblWorkOrderCategory wc ON wo.CategoryID ...

Is there any good code analysis/refactoring tools for T-SQL besides RedGate?

I've used RedGate in the past, but the feature-to-price ratio really sucks. At $369 for one-year it is far too expensive when compated to much richer applications like CodeRush and Resharper. ...

Specify data type with column alias in SQL Server 2008

Imagine I have the following SELECT statement in a view (SQL Server 2008): SELECT (SELECT CASE WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y' THEN 1 ELSE 0 END) AS OOS FROM HISTORY The column OOS ends up being of type int, but I'd like it to be of type bit. How can I acco...

DataSet TableAdapter throwing OVER SQL construct or statement not supported.

I have a t-sql query written with this sample help. SELECT t.gName AS 'Product' , isnull(SUM(CASE WHEN t.Col = 1 THEN t.Quantity END),0) AS '180ml' , isnull(SUM(CASE WHEN t.Col = 2 THEN t.Quantity END),0) AS '375ml' , isnull(SUM(CASE WHEN t.Col = 3 THEN t.Quantity END),0) AS '500ml' , isnull(SUM(CASE WHEN t.Col = 4 THEN t.Quantity END)...

Writing a simple SQL Server 2005 dynamic sql

I am writing a fairly simple SQL query in SQL 2005, but am running into an issue and can't figure out what is wrong. For documentation purposes, the query has to be a dynamic sql A snippet of my query is: @RecCreatorID int .... .... IF (@RRecCreatorID IS NOT NULL) Begin Set @strSQL = @strSQL + ' AND RecCreatorID = @RecCrea...

Migration tool from TSQL to PL/SQL?

We need to migrate our database from MSSQL to Oracle and we have over 100 stored procedures written in PL/SQL TSQL. I know its a long shot, but has anybody ever successfully used an automatic migration tool to do the work? I've seen some tools on the web, but I have no way to judge their ability to handle various code. Any personal exper...

TSQL- Export results to Excel returns single-threaded apartment mode error

I want to export the results from a sproc to Excel. Thus, between the exec and SELECT statements I insert the following: INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\testing.xls;', ' SELECT Field1, Field2, Field3 FROM [Sheet1$] ') Which returns the following error: OLE DB provider 'Microsoft.Jet.OL...

Any hidden pitfalls changing a column from varchar(8000) to varchar(max)?

I have a lot (over a thousand places) of legacy t-sql code that only makes INSERTs into a varchar(8000) column in a utility table. Our needs have changed and now that column needs to be able to handle larger values. As a result I need to make that column varchar(max). This is just a plain data column where there are no searches prefor...

Finding similar names in multiple tables

I have multiple tables with different customer names. I am trying to find out how many times the same name is in a table. The challenge here is that someone could have entered the name as "John Smith" or "Smith, John". There are 40,000 rows in each table and over 40 different tables. I am trying to query somehow without knowing the name...

Writing a dreaded SQL search query.

I am working on a search query that does not seem to work. The complete query is: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[usp_Item_Search] @Item_Num varchar(30) = NULL ,@Search_Type int = NULL ,@Vendor_Num varchar(10) = NULL ,@Search_User_ID int = NULL ,@StartDate smalldatetime = NULL ...

SQL Server 2005 bigint missing?

I am trying to set a column as a 64-bit integer, but my only available options are tinyint, smallint and int. Where did bigint run off to? Notes: I'm using Access 2008 to access my SQL Server. ...

Is DECLARE what I should be using in order to reference a created field within the same program

I'm trying to create a new field and then later on in my program reference that new field within a case statement, but I can't seem to get the syntax right - my error message says there's an error near the '='. Here's my code: declare @source_sys_obligation_id varchar(40); if facility_utilization in ('F') set source_sys_obligati...

Writing a dreaded SQL search query (2nd phase)

I am working on a search query (with an asp.net 3.5 front end) which seems quite simple, but is quite complex. The complete query is: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[usp_Item_Search] @Item_Num varchar(30) = NULL ,@Search_Type int = NULL ,@Vendor_Num varchar(10) = NULL ,@Search_User_I...

SQL Join Ignore multiple matches (fuzzy results ok)

I don't even know what the name of my problem is called, so I'm just gonna put some sample data. I don't mind fuzzy results on this (this is the best way I can think to express it. I don't mind if I overlook some data, this is for approximated evaluation, not for detailed accounting, if that makes sense). But I do need every record in TA...

use if-else logic in where clause in T-SQL

Is there any way to use if-else logic in where clause in T-SQL? or do I have to implement the logic by using subquery? ...

How do you update a DateTime field in T-SQL?

The following query does not update the datetime field: update table SET EndDate = '2009-05-25' WHERE Id = 1 I also tried it with no dashes, but that does not work either. ...

T-SQL: Double-cocked Trigger, Polling or a .NET Async Thread?

So i bet i got you curious with that title. =) Here's the scenario - simplified for the cause. Website: ASP.NET 4 Web Forms. Database: SQL Server 2008. DAL: LINQ-SQL. In the DB, we have two tables: Foo and Bar. Relationship is 1-1, and Bar has a foreign key constraint to Foo. In other words, you need to create Foo first, then use t...

Is there any reason why you cannot select a statement as a bit in SQL Server?

Hi, I am wondering why the following fails: SELECT price<500 as PriceIsCheap and forces you to do the following: SELECT CASE WHEN (price<500) THEN 1 ELSE 0 END as PriceIsCheap When, as per the answer to this related question, the conversion table says that an implicit conversion should occur. ...