SQL Server Blogs

Voices from the UK SQL Server Community
All Tags » T-SQL (RSS)

Browse by Tags

  • CTE in a View

    It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible create view numbers as with numbers ( n ) as ( select 1 as n union all select n + 1 from numbers where n < 10000 ) select n from numbers option ( maxrecursion 0 ) Which when created results...
    Posted to Madhivanan (Weblog) by Madhivanan on Sat, Jul 11 2009
  • Export to Excel - data length exceeds 255 characters

    If you use Data Transaformation Services to send data to EXCEL, you may get error about the size of the cell if length of data exceeds 255 characters. This KB from microsoft explains how to solve it;en-us;281517
    Posted to Madhivanan (Weblog) by Madhivanan on Mon, Jun 22 2009
  • Quirky update in sql server

    When you use variables in update statement and update the columns with the values of variables which keep on changing for each row , you can simulate many things that are done using Loop or a Cursor declare @temp table ( product_name varchar ( 100 ), Value int , sequence int ) insert into @temp values...
    Posted to Madhivanan (Weblog) by Madhivanan on Wed, Jun 10 2009
  • BCP and numeric data field with scientific notation

    There is a known issue in SQL Server 2005 with importing data using bcp.exe or BULK INSERT methods from character files that contain numeric values written using scientific notation, like 2.044E10. It was not a problem in versions prior to 2005 because bcp for SQL Server 200 and 7.0 converted such values...
    Posted to Piotr Rodak (Weblog) by Piotr Rodak on Sun, May 17 2009
  • Removing unwanted characters

    Sometimes when you import data from other system , you may want to clean up data by removing unwanted characters You can use Replace function . Suppose you want to remove the character ^ from the string declare @str varchar ( 100 ) set @str = 'test^ string' select @str = replace ( @str , '...
    Posted to Madhivanan (Weblog) by Madhivanan on Mon, May 11 2009
  • How to find size of backup files in a folder?

    I was asked to findout total size of all the backup files in the folder As I didnt have access to view the folder of the server , I used this . I hope it may be helpful create table #t ( files varchar ( 1000 )) Insert into #t EXEC master .. xp_cmdshell 'dir e:\backup' select substring ( size_files...
    Posted to Madhivanan (Weblog) by Madhivanan on Thu, May 7 2009
  • Import from Text file - Single column to multiple columns

    One of the users asked this question in the SQL forum I have data in the text as follows AccountNo: 00234543 AccountName: Kickser City: Chicago Country:USA AccountNo: 00234543 AccountName: Annis City: Seatle Country:USA AccountNo: 12234456 AccountName: pargenezzer City: NYC Country:USA ... .... .......
    Posted to Madhivanan (Weblog) by Madhivanan on Tue, May 5 2009
  • Tables with no Primary and Foreign keys

    John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity. What if you want to know list of tables that have no primary and foreign keys? Here is the query select table_name from INFORMATION_SCHEMA.TABLES where objectproperty ( object_id ( table_name ), 'TableHasForeignKey'...
    Posted to Madhivanan (Weblog) by Madhivanan on Fri, Apr 10 2009
  • MARS - does anyone use it?

    I read recently about MARS - Multiple Active Result Sets, functionality that came with SQL Server 2005. I tried to find some 'real life' example of using MARS. Most of the resources I found showed examples on AdventureWorks database and they were, to say the least, showing how NOT to access the...
    Posted to Piotr Rodak (Weblog) by Piotr Rodak on Wed, Apr 8 2009
  • Last Backup/Restore date of database

    If you want to know the last Backup/Restore date of database, use the following --Last Backup date of database --Method 1 In Management Studio,Expand database,Right Click on the database and click properties Refer Last Batabase Backup which is availalbe under General --Method 2 select database_name ...
    Posted to Madhivanan (Weblog) by Madhivanan on Fri, Apr 3 2009
Page 5 of 10 (93 items) « First ... < Previous 3 4 5 6 7 Next > ... Last »
Powered by Community Server (Commercial Edition), by Telligent Systems