tsql

tsql : best way to update a db table with a csv file

I have a products table in sql server 2005 which needs to be updated by certain fields in a csv file. Both files have a vendor part number which can be linked to where I can update the products.discontined field with another in the csv file. My question is what is the best way to approach this? I've considered creating a odbc conne...

T-SQL equivalent of =rand()

I have several content tables that I want to fill up with random paragraphs of text. In MS Word, I can simply put =rand() and presto! I get three paragraphs of fresh-off-the-press text. Is there a SQL script/command that I can use to generate random dictionary words using t-sql? ...

T-Sql 2005 Adding hours to a datetime field with the result within working hours

I have two Datetime fields that I wish to add together. They are in the following format: '01/01/1900 00:00:00'. The main issue with this is that I want the calculation to only include working hours. The working day is between 08:30 and 17:30 and does not include weekends: Also if the first field starts out of the working day or is on ...

Removing duplicate records.

Hi, I am using the following query select SS.sightseeingId, SS.SightseeingName, SS.displayPrice, SST.fromDate from tblSightseeings SS inner join tblSightseeingTours SST on SS.sightseeingId = SST.sightseeingId where SS.isActive = 1 and SS.isDisplayOnMainPage = 1 and getting result like this 2 Dinner Cruise Bat...

TRY and RAISERROR in T-SQL

Having a small issue and wondering if I'm using these correctly. In my SQL script is have BEGIN TRY // check some information and if there are certains errors RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR // Complete normal process if no errors encountered above PRINT 'IMPORT SUCCEED...

What is the effect of omitting size in nvarchar declaration ..

Hi, I usually defining size when declaring parameters in my SP, like : @myParam nvarchar(size) or when I casting or converting: CAST(@myParam AS nvarchar(size)) Recently I removed size from my CAST functions like: CAST(@myParam AS nvarchar) and I'm bit worried if that is going to come and bite me when least expected :-(, since I...

Iterate Xml Column elements and comapre against Xml Variable

Hi, I'm not sure how to word this correctly but I'm very (very) new to XML in Sql Server. I have an XML Column defined in a table and I want to retrieve the id of the record if the data in the Xml Column matches elements in a predefined list. The data looks a bit like this: <Parameters> <Parameter> <Name>Param1</Name> ...

T-SQL match special characters. Pre should match Pré.

I have a list of company names and the user has to enter his company name to get into the system. Let's say we have the company "Pré ABC", now I want the user to be able to type "Pre" or "Pré". First I thought this was build-in functionality of the LIKE statement, but unfortunately it isn't. Any thoughts? ...

Copy Trigger slows database operations down considerably

Hello Our original database has called first, middle, last for names of people. We have now created fields called firstname, middlename, and lastname. While we are going through this transition I have created a trigger to copy data from first to firstname, middle to middlename, and last to lastname anytime one the previous fields receiv...

Need help with a SQL query that combines adjacent rows into a single row

Hi all, I need a solution to a problem that has the table structure as listed below. Input 1 1/1/2009 Product1 2 2/2/2009 Product2 3 3/3/2009 Product3 4 4/4/2009 Product4 5 5/5/2009 Product5 Output 1 1/1/2009 2/2009 Product1 2 3/3/2009 4/4/2009 Product3 3 5/5/2009            Product5 I tried using CTE. But was not ...

SQL converting Text fields

I'm bumping into somenull fields in a SQL2005 db. Some report Null and others have values like 1.00 or 713.00. I'd like a bullet proof way to convert the 'Null's to 0 and the '1.00' and '713.00' values into Money types. ...

How to reformat and sort dates stored in nvarchar

I am trying to display Month and YY part of the date column such that it should appear as Original Format Apr 12 2009; Feb 20 2009; Dec 24 2008; May 18 2009; Jan 8 2009; Dec 6 2008; Apr 19 2009; Jan 4 2009; May 13 2009; Jan 5 2009 From these dates the day part should be ripped off such that it appears as "Apr 09" ...

tsql : how do you query with dashes in column names?

I have the query select * from products p, products_temp t where p.ManufacturerPartNumber = t.[INV-PRICE-VENDOR-PART] where the column names have dashes in them which SQL Server 2005 seems to automatically add brackets to. What is the correct way of accessing this in a query? I've tried with brackets and without the brackets and ju...

Passing an array of ID's to a stored procedure

I have stored procedure, i want to send it an array of values. When i send more than one i'm getting an error Error converting data type varchar to numeric. Here is the code. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_getTransfer] @TRANSFER_ID int = null, @COMPANY VARCHAR(5),@FORMTYPE VARCHAR(30), @TRANSF...

How do I best get the top 2 unique rows when a JOIN is involved?

I have this query: select top(2) property_id_ref ,image_file ,property_name from property_master a inner join image_master b on a.property_id=b.property_id_ref inner join customer_master c on a.customer_id=c.customer_id When I execute it, I get the following result: 512 ~/propertyimg/3954493 id_1.jpg Commerci...

How do I update two tables that have a reference table in SQL Server?

I have 3 tables. 1. Users 4 Cols UserID - UserName - RealName - Flags 2. UsersGroups 2 Cols UserID - GroupID 3. Groups 3 Cols GroupID - GroupName - Flags What I want to do is select a specific UserName ie USERA and update the Flags column. but I also want to update the Flags column in the Groups table to the same value. The only co...

best way to convert and validate a date string

I have a single char(8) variable formatted as ddmmyyyy in a stored procedure (quality and validity of this value is unknown and beyond my control). What is the best most efficient way to move the value into a datetime variable, and throw an error if it is not valid datetime. DECLARE @Source char(8) DECLARE @Destination datetime ...

Ambiguous column name error, how do I fix it?

1. Users 4 Cols UserID - UserName - RealName - Flags 2. UsersGroups 2 Cols UserID - GroupID 3. Groups 3 Cols GroupID - GroupName - Flags What I want to do is select a specific UserName ie USERA and update the Flags column. but I also want to update the Flags column in the Groups table to the same value. UPDATE dbo.Users SET Flags = ...

Detect Sql Express From TSQL

Hi, I need a safe (i.e consistent, robust) way of detecting whether or not the sql server I'm accessing is Sql Express. I think I'd prefer to be able to do this from TSQL, since I already have a connection string and all the libraries I need to execute TSQL (this should help avoid issues with whether or not WMI is installed/running, the...

functional reason why stored procedures don't support INSERT/EXECUTE INTO?

In SQL Server, there's no way to create a temp table on the fly from the results of a stored procedure, ala: CREATE TABLE #temptable AS EXEC spMyStoredProc or EXEC spMyStoredProc INTO #temptable or something like that. Instead, you have to know the SP layout beforehand, and have to do something like this: CREATE TABLE #temptable (...