sql-server-2005

Problem with case and status

When I don't have right side I get NULL on column StatusOfDeduplication instead 5. What is wrong with this query ? select c.Code AS Code, c.DefaultName AS Name, c.Status AS Status, case cp.TargetCodeStatus when Null then 5 else cp.TargetCodeStatus end as StatusOfDeduplication from Cities c LEFT JOIN CityPackages cp ON cp.TargetCode ...

SQL List of year from this year to 5 years back, then used as parameter in stored sproc.

First I need to create a sproc that will give me just a list of years from this year back to 5 years before this year. I was thinking of somehow using a table variable? After that I need to use that value as a parameter in another sproc. How would I then say show all Dates that contain the year that is picked? I know how to write the pa...

group by clause

i have a table called table1 and it has following columns. suppose there are records like localamount is 20,000, 30000,50000, 100000 then as per my condition i have to delete records from this table according to the group by set of site id, till id, transid,shift id where localamount exceeds 10,000... the rest of the records can be avai...

SQL why wont this let me select a blank value and a value?

I have the following UDF. CREATE FUNCTION [dbo].[udf_GenerateVarcharTableFromStringList] (@list varchar(MAX), @delimiter char(1) = N',') RETURNS @tbl TABLE ([Value] varchar(200)) WITH SCHEMABINDING AS BEGIN DECLARE @chrind INT DECLARE @Piece nvarchar(4000) SELECT @chrind...

Creating Hierarchy in SQL server

I have data in following format Table1 e_id e_name e_type ------------------------- 1 CBC 2 2 ABC 3 3 N2 1 4 CBC1 3 5 ABC1 3 6 N1 1 table2 N_ID N_Name --------------- 3 N2 6 N1 Table3 N_ID E_ID ------------ 3 1 3 2 3 3 6 4 6 5 6 6 And...

SQL Server 2005: Delete Optimization

Hi, Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table? Sample: DELETE FROM DeletedProducts WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo Or is NOT EXIST a better way to perform this. Note: ProductFileInfo has over 20 Million...

How do I call this function in a sproc?

I got this function from someone on here: create FUNCTION [dbo].[fnSplitString] (@s varchar(512),@sep char(1)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop...

Using Encrypt=yes in a Sql Server connection string -> "provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value."

Hi geniuses (is that genii?) I'm using 'Encrypt=yes' in a Sql Server connection string, because i need the tcpip traffic to be encrypted, but on opening the connection i get the error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 ...

Convert string to CSV format

Hello, How can i make the following query to work :- Declare @Ids as varchar(20); Set @Ids = '1,2,3,4'; Delete from tblProduct Where ProductId in (@Ids); Thanks in advance :) ...

Filteration in where clause: syntax error?

Please help me to sort this query: WHERE RECEIPT_DATE BETWEEN( Coalesce(@FROM_DATE,RECEIPT_DATE) AND Coalesce(@TO_DATE,RECEIPT_DATE) ) AND OFFICE_ID=Coalesce(@OFFICE_ID,OFFICE_ID) Error: Incorrect syntax near the keyword 'AND'. ...

Read uncommitted isolation level doesn't allow to read entries

Hello. I need to read dirty entries in Sql server but I don't understand why I can't. Hope you will help. I have two tabs in management studio with following code Tab 1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO BEGIN TRAN UPDATE decision.tRequests SET IsComplete = 0 WAITFOR DELAY '00:00:25.000' COMMIT TRA...

SQL conundrum, how to select latest date for part, but only 1 row per part (unique)

I am trying to wrap my head around this one this morning. I am trying to show inventory status for parts (for our products) and this query only becomes complex if I try to return all parts. Let me lay it out: single table inventoryReport I have a distinct list of X parts I wish to display, the result of which must be X # of rows (1 r...

group by clause query

i have a table with values like this, i want to group by cusotmer name where as sum of the local amount should exceed 50000 else i need to delete those records which do not satisfy? how to achieve it in sql server 2005? TRN 259 3 9/9/2010 6622 68667(Rs) ABHIJIT KATARE TRN 260 3 9/9/2010 6622 14635(Rs) ABHIJIT KATA...

Problems using PPTX iFilter in SQL Server 2005

I'm having issues with using the Office iFilters. I installed them to our server, and verified that .PPTX was an available extension using the sys.fulltext_document_types system view in SQL Server 2005. I then rebuilt our FT catalog, and reviewed the logs to make sure everything was fine. No errors. I did a full text search in my tab...

What are the "best practices" to remove "non-unique" values from HUGE table?

Step 1: Loading data via "bulk insert" from .txt (delimited) file into Table 1 (no indexes etc.) bulk insert Table_1 from '\\path\to_some_file.txt' with ( tablock, FORMATFILE ='format_file_path.xml') Via format file I map output Column data types to avoid further conversions (from Char to Int for example) Step 2: OUTPUT the result (...

SQL how to extract middle of string

I want to only get the unit number out of this string: '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>' The note is always exactly the same but the unit number changes. How do I extract just the unit number? Thanks! ...

SQL Server 2008 - How fast are table joins across databases (same server) vs. tables in the same database?

We're customizing a CMS that has a few hundred tables in it. We are considering moving our custom tables to a separate database on the same server. Should we expect much of a slow down in SQL Server? There will be many multi-join stored procedures joining data across the CMS database and our custom database. ...

SQL use nested select in middle of inner join

Is it possible to use a select in the middle of joining... I am trying to do the following: FROM tblorders o INNER JOIN tblunits u on o.id = u.orderid INNER JOIN ((SELECT ,Min(n.date) as [MinDate] from tblNotes n Where n.test = 'test') te INNER JOIN tblnotes n on te.id = n.id ...

group by clause in sql server 2005

i have table called table1 and it looks like the below record type tran_ref_number amount customer_name TRN 123 15000 sara TRN 234 25000 inba TRN 345 20000 rajiv TRN 45...

Select top 1 with UPDLOCK and READPAST sets exclusive lock on the entire table

I'm using the UPDLOCK and READPAST sql hints in a stored procedure in order to implement a sort of a table queue (I say a sort because I select top 1500 instead of a top 1, and I don't delete the rows after I select them. For more details, see question http://stackoverflow.com/questions/3636950/return-unlocked-rows-in-a-select-top-n-quer...