Browse by Tags

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 http://support.microsoft.com/default.aspx?scid=kb;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
Table level Backup/Restore

It may be very useful if we have feature to take backup or restore a table I have posted my suggestion here Table level Backup/Restore What do you think of it?

Posted to Madhivanan (Weblog) by Madhivanan on Thu, Apr 2 2009

Page 5 of 10 (92 items) « First ... < Previous 3 4 5 6 7 Next > ... Last »