tsql

Splitting Address Lines in MS SQL

Hi guys, I have this setup: an output table where my results will end up consisting of [FirstName] [LastName] [Address1] [Address2] [Address3] [ZipCode] [City] [Country] The original data consists mostly of line feed split address lines, some are split by commas, and there is one name field. My original data tables look simply like...

Finding the database name from tsql script

Is it possible to programmatically find the database context from a tsql script? ie the context that changes when you add a USE . I ask because I am not using a USE, and would like to find the database name the script is running on. ...

T-SQL How to check if DataTime contains a Date string ?

Hi, I have a DateTime column row and I declare a date string: row: 2010-08-27 13:45:55 my string: '2010-08-27' Hi to check if that string is in that row ? I tried that query: declare @year as nvarchar(4) declare @month as nvarchar(2) declare @day as nvarchar(2) set @year = '2010' set @month = '08' set @day = '23' select * FROM...

Casting/Converting Chars to Varchars by UDF for SSRS

The database which I report from most often is not properly typed. Almost every field, with the exception of some integers and datetimes, are CHAR fields. This can cause problems with SSRS layouts because some fields have tons of trailing spaces. This causes layouts to be messy and such. I often rtrim these fields in my scripts to prev...

SQL Server Comma Separated value among columns

I want to select columns as comma-separated values by doing something like: select column1+','+column2+','+column3+','+coulmn4 from someTable except if any of the columns hold null values i have to skip that column from adding comma how to do this is SQL Server? [All columns are of type varchar so no casting needed] ...

TSQL Table Transformation Fields => Columns

I have the following table layout. Each line value will always be unique. There will never be more than one instance of the same Id, Name, and Line. Id Name Line 1 A Z 2 B Y 3 C X 3 C W 4 D W I would like to query the data so that the Line field becomes a column. If the value exists, a 1 is applied in the field ...

Good practice to modify MSDB stored procs?

At work, we've working on SQL Server 2008 now, and my boss seems a little bit paranoid about security, so instead of having people be sysadmin (or whatever they need to be to own/modify jobs) he's been editing stored procs in MSDB (such as sp_update_job) and adding a role to the TSQL that checks for required permissions (such as adding S...

help with t-sql query

Hi Based on the below tables Table_A Id RelatedId --------------- 1 1 1 2 1 3 2 4 2 5 2 2 3 7 3 8 4 9 Table_B RelatedId Name -------------- 2 A 3 B I want to get all the rows from Table_A which have atleast one matching relatedid in Table_B. The Ids from Table_A that do not have match in Table...

stored proc returns no results, but query does.

Hi all, I have just managed to enable full-text searching to make searching for products in my system easier. However, I am using an sp that was built by someone else and it is not returning any results for "Duty Call". In actual fact I am searching for "Call of Duty", a product in the system. If I type "Call of Duty" it does return a r...

when should we use sql cursor

hi all tell me when should i use cursor? thanks saj ...

I Need Countries List in T-SQL

I have a Country table which has CountryName column. I need a ready made list in DDL to populate in the column CountryName. The table resides in SQL Server 2008. ...

SQL 2005 - Table valued function compiles ok but throws Incorrect syntax near .. when selected

This table valued function compiles ok, alter function [dbo].[ftsls031nnnHades](@withExpiredEntries smallint ) returns @t table( comno varchar(3), t$cuno varchar(6), t$cpgs varchar(6), t$dile float, t$qanp float, t$stdt varchar(10), t$tdat varchar(10), ...

User Defined Table Types Not Available

Do you need a specific version of Sql Server 2008 to have TVP (Table Value Parameters) or Table Types? I don't have the folder under types and I get an error when trying to create one: CREATE Type EntityTable AS TABLE (EntityId int ,EntityName varchar(max) ,EntityFriendlyName varchar(max) ,IconPath varchar(max) ,EntityDescription ...

Database Naming Conventions by Microsoft?

I found Naming Guidelines from MSDN, but is it any guideline for MSSQL database from Microsoft? ...

Select / Insert version of an Upsert: is there a design pattern for high concurrency?

I want to do the SELECT / INSERT version of an UPSERT. Below is a template of the existing code: // CREATE TABLE Table (RowID INT NOT NULL IDENTITY(1,1), RowValue VARCHAR(50)) IF NOT EXISTS (SELECT * FROM Table WHERE RowValue = @VALUE) BEGIN INSERT Table VALUES (@Value) SELECT @id = SCOPEIDENTITY() END ELSE SELECT @id = RowID ...

How to copy everything except data from one database to another?

Hi, In T-SQL (Microsoft SQL 2008), how can I make a new database which will have the same schemas, tables, table columns, indexes, constraints, and foreign keys, but will not contain any data from the original database? Note: making a full copy, then removing all data is not a solution in my case, since the database is quite big, and s...

TableName using stored procedure in SQL

I have some problems to passing the @TableName inside a Nearby procedure to use in one StoreLocator. I need to get in 3 tables. I have tested using QUOTENAME but the problem is always here. Can someone help me to fix this problem. Thanks ALTER PROCEDURE [dbo].[GetNearbyTable] @Table sysname, @CenterLatitude FLOAT, @Cente...

Stored Procedure Problem

I have a problem with accessing output in the stored procedure below DbParameter DbParm = command.CreateParameter(); DbParm.Direction = ParameterDirection.Output; DbParm.ParameterName = "@SomeValue"; DbParm.DbType = DbType.Int32; DbParm.Value = null; command.Parameters.Add(DbParm); Afte...

T-SQL Query : Getting Child nodes of a parent

I have a table with the following schema : ID , CatID, ParentCatID, SiteID I want to get all the sites that belong to the categories that are the roots ( means their ParentCatID = 0) and all their descendants. for example : ID , CatID, ParentCatID, SiteID -------------------------------- 1 , 2 , 0 , 3 1 , 4 , 2 ...

Duplicating a TABLE using Microsoft SQL Server Mangement.

Need to duplicate a TABLE using Microsoft SQL Management Studio 2008 The TABLE needs to duplicate all table row (Primary Key) ID as well. Thanks. ...