Feeds:
Posts
Comments

Archive for the ‘SQL’ Category

This is quite a simple one for the DBAs. If you get stumped with a SQL agent error for your machine name, details are listed as follows:

Error: Could not find server in sysservers. Execute sp_addlinkedserver to add the server to sysservers. The statement has been terminated.

Solution:

  1. Open SSMS and verify that SQL Server name is correct
    use master
    Select @@Servername
    
  2. Run the following command
    Use Master
    GO
    Sp_DropServer 'myServer123'
    GO
    Use Master
    GO
    Sp_Addserver 'myServer786', 'local'
    GO
    
  3. Restart SQL Server service
  4. Re-run command on step#1. This should return back the correct machine name

Happy coding 🙂

Read Full Post »

Here is a handy command to clear all tables on your (local) database.

  1. Select the Database in SQL Server Management Studio, and hit New Query
  2. Run the following command
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

That is good for dropping all tables. Most of the time, you’d like to delete all table data. Also, to overcome tables associations on constraints/keys, etc. Use the following in this case:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Note: You cannot use  TRUNCATE with above syntax!

Happy coding 🙂

Read Full Post »

For the life of me, I’m not sure I deleted databaseS related with SQL reporting services (on my dev box). And now, I would like to recreate them.

Solution:

  1. Navigate to Start → Programs → SQL Server 2008 R2 → Configuration Tools → Reporting Services Configuration Manager
  2. Click Databases → Change Databases

  3. Click Create new report server database, and follow the prompts.. All done 🙂
    This would create two new databases  – ReportServer and ReportServerTempDB

Happy reporting 🙂

Read Full Post »

If your company hasn’t purchased software assurance for SQL 2005, this one is for you ..The following is a Microsoft announcement on their licenses page..

================

With the release of SQL Server 2008 R2 (April price lists), there will be

15% increase in the processor pricing of SQL Server 2008 Enterprise Edition and a 25% increase in the processor pricing of SQL Server 2008 Standard

Expected release date: Launches in the first half of calendar year 2010

Reference: Click here

Read Full Post »

There is a SQL 2008 ½ a day training on the 29th of Jan – which is free offer from one of the consulting companies. Might be good if anyone would like to attend this..

Details:
Sydney SQL Server 2008 R2
Event Date: 29.01.10

WARDY IT Solutions is proud to announce that they will be delivering the first SQL Server 2008 R2 workshops in Australia.

This workshop is a free ½ day Hot Lap. A Hot Lap is a quick and concise way of finding out what is new in SQL Server 2008 R2 and how these changes will improve database operations, developer efficiency and enable self-service Business Intelligence solutions.

This workshop will also include a light breakfast and morning tea.

When: 8:00am – 12:00pm, Friday the 29th of January 2010.
Where: Microsoft Sydney, 1 Epping Rd, North Ryde NSW 2113

Happy learning 🙂

Read Full Post »

Often you’d be given a list of reference Ids, and you would like to pass these Ids to a stored procedure, to retrieve the records.

The easiest way is to create a function that would return a table and then use this function. This can be used from your stored procedure to filter the required ..

Here is the script for the handy function .. 

CREATE FUNCTION [dbo].[ListToTable]
(@list varchar(7000))
   RETURNS @tbl TABLE (number int NULL) AS
   BEGIN
         DECLARE @pos   int,    
         @nextpos  int,
          
         @valuelen   int
   SELECT @pos = 0, @nextpos = 1
   WHILE @nextpos > 0
   BEGIN
     
     SELECT @nextpos charindex(‘,’, @list, @pos + 1)
     
     SELECT @valuelen = CASE WHEN  @nextpos > 0
               
                             THEN @nextpos
                             
                        ELSE len(@list) + 1
                        
                        END @pos 1
  
     INSERT @tbl (number)
     
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
     
     SELECT @pos = @nextpos
   END
  RETURN
END

 This is how to use it within a stored proc:  

DECLARE @receivedIds AS VARCHAR(MAX)
SET @receivedIds = ‘12,13,16,18,20,65,91’ — Wud be a incoming param
SELECT number FROM 
   dbo.IntListToTbl (@receivedIds)

  

Happy coding 🙂 

Read Full Post »

Having a small issue with a stored proc executing a “Delete FROM tablename” statement against a (temp) table containing a huge number of rows. In this instance a “truncate table tablename” would have suited better.

From the MSDN documentation
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

  • Less transaction log space is used.
    The DELETE statement removes rows one at a time and records an entry
    in the transaction log for each deleted row. TRUNCATE TABLE removes the
    data by deallocating the data pages used to store the table data and
    records only the page deallocations in the transaction log.
    
  • Fewer locks are typically used.
    When the DELETE statement is executed using a row lock, each row in
    the table is locked for deletion. TRUNCATE TABLE always locks the
    table and page but not each row.
  • Without exception, zero pages are left in the table.
    After a DELETE statement is executed, the table can still contain 
    empty pages.For example, empty pages in a heap cannot be deallocated 
    without at least an exclusive (LCK_M_X) table lock. If the 
    delete operation does not use a table lock, the table (heap) will 
    contain many empty pages. For indexes, the delete operation can leave 
    empty pages behind, although these pages will be deallocated quickly 
    by a background cleanup process.

 

 Extrapolating on this – if your intention is to simply remove all records from a table, without the need for a rollback – truncate table is significantly more performant.

Happy terminating.. err – truncating 🙂

Read Full Post »

Older Posts »