Archive for the ‘MS SQL’ Category
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.
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
from @setcodeid into @codeid
while @@FETCH_STATUS = 0
— do you stuff here…
fetch next from @setcodeid into @codeid
A very effective way if you just want to get total row count of table. Check full article here.
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 :
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.
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.