Sandip's Programming Zen

An attempt to share tech/coding experiences

Archive for the ‘MS SQL’ Category

Generate SQL script with schema and data for a database

leave a comment »

When we need to quickly send an app which is under development for testing to someone and don’t need to worry which version of SQL server he may have, we should send SQL script for entire database instead of sending database backup file (because if SQL server version will not match, it won’t be restored, which happens often).

To do this easily there is a hidden tool which comes when you install any version of SQL server (developer, enterprise or express). It’s called Database Publishing Wizard, which lets you generate SQL script for your entire database with schema and data. The receiver of this SQL script file just needs to create an empty database and run the script.

Location of the wizard is normally here (in case of SQL server 2008 Express):

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe

Hope this helps.

 

Advertisements

Written by Sandip

December 17, 2011 at 2:30 pm

Posted in MS SQL

MS SQL: Using dynamic sql in Cursor

leave a comment »

Here is sample code for using dynamic sql in cursor , you have to set two parameters @tablename and @fieldname and it will build a select query for the given field and table.

declare @sql nvarchar(1000)
declare @tablename nvarchar(50)
declare @fieldname nvarchar(50)

set @tablename = ‘MyTableName’
set @fieldname = ‘MyFieldName’

select @sql = N’set @setcodeid =  cursor for select ‘ + @fieldname + N’ from ‘ + @tablename + N’; OPEN @setcodeid’;

declare @codeid varchar(100)
declare @setcodeid cursor
exec sp_executesql @sql,N’@setcodeid cursor OUTPUT’,@setcodeid OUTPUT

fetch next
    from @setcodeid into @codeid
        while @@FETCH_STATUS = 0
            begin
                — do you stuff here…

                fetch next from @setcodeid into @codeid
            end
close @setcodeid
deallocate @setcodeid

Written by Sandip

April 22, 2010 at 7:36 am

Posted in MS SQL

Tagged with ,

MS SQL: Alternative way to get the table’s row count

leave a comment »

A very effective way if you just want to get total row count of table. Check full article here.

To get the total row count in a table, we usually use the following select statement:

SELECT count(*) FROM table_name

This query performs full table scan to get the row count. You can check it by setting SET SHOWPLAN ON for SQL Server 6.5 or SET SHOWPLAN_TEXT ON for SQL Server 7.0/2000. So, if the table is very big, it can take a lot of time


There is another way to determine the total row count in a table. You can use thesysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command

Written by Sandip

April 9, 2010 at 11:52 am

Posted in MS SQL

Tagged with

SQL Performance and Recovery Model

leave a comment »

Recently I had a case where an application which was working fine suddenly started slowing down and apparently there were no code changes made. After checking around at Database properties found that Recovery Model was set to “Full”. In fact it was not needed according to the backup policy as only full backup was scheduled, not at the level of transaction log.

Setting the Recovery Model to “Simple” solved the problem and application was back on acceptable performance.

Check this link to know more about Recovery Model :
http://msdn.microsoft.com/en-us/library/aa173531.aspx

Written by Sandip

February 18, 2009 at 12:58 pm

Posted in MS SQL

Moving DB From SQL Server 2008 to SQL Server 2005

with 2 comments

I have upgraded by SQL Server Express addition from 2005 to 2008, no problem everything runs smoothly.

Now I have to copy the database on user’s machine who uses SQL server 2005, Oops it does not work, gives following error.

The database ‘C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\MY DOCUMENTS\####\#########\############\DATA\######.MDF’ cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported.

I have anticipated some problem and thus changed the compatibility of the database to SQL Server 2005 so hoped it will work fine, but No it does not. 

So far I have not found any solution to this problem so I just generated SQL Script for all the objects and ran it on the target server as well as data imported using ms excel.

Written by Sandip

October 25, 2008 at 1:01 pm

Posted in MS SQL

Tagged with

Interesting SQL Server stuff

leave a comment »

1. This article explains how to track SQL Server 2005 deadlocks using Profiler tool.

2. An open source project at coldeplex to compare two SQL Server 2005 databases.

Written by Sandip

June 30, 2008 at 5:41 am

Posted in MS SQL

Tagged with

Migrating SQL Server 2000 database to SQL Server 2005

leave a comment »

SQL Server 2005 has a feature called “Copy Database” (you can right click on any database or server instance to find it). The function copies database from target sql server 2000 to the sql server 2005 while still keeping it online for target database users.

In my case, I ran through the migration wizard and in the last step which it actually runs the package which migrates database, it failed. It did not shown any proper error message so that I can find out what went wrong. I did it again and this time making sure I exactly follow the process but result was same.

So it did not go smooth. So I opted to do it crude way, by detaching and reattaching the database mdf file in sql server 2005. That worked. Then I had to migrate all dependent sql jobs and DTS packages manually as well.

Written by Sandip

June 22, 2008 at 10:56 am

Posted in MS SQL