MS SQL Server 2008 new features

Data Compression

SQL Server 2008 gives us the ability to compress data and save on disk space. Without getting into many specifics, data in SQL Server can be compressed at the page level. This means that when you compress a table, it actually does it a page at a time. I'd advise you to tread lightly with data compression. You should make sure you do your research and testing before implementing compression in your environment. That said, when disk space is at a premium, you can use this feature to pack more data onto the platters.

Backup Benefits
SQL Server 2008 provides backup compression. With backup compression, you have the savings of file size built right into the native backup.

To use backup compression, you just need to add the WITH COMPRESSION option to a BACKUP DATABASE statement as shown below:

BACKUP DATABASE Adventureworks2008

TO DISK = 'D:\Backup\AdventureWorks.bak'

WITH COMPRESSION

Here's an example of the compression ratio: a backed up 965MB database without compression resulted in a 636MB backup file. Using compression, the same database produced a 147MB backup file.

Learn to Merge
The new MERGE statement in SQL Server 2008 obviates the need for "IF-THEN" logic to decide whether a row needs to be inserted, updated or deleted. MERGE allows you to take care of the logic and the modification all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's a quick example of using MERGE:

MERGE tbl_address AS current_addresses

USING (

SELECT customer_objid = address_label,

addressline1, addressline2, city, region, country, zip

code, is_deleted

FROM @addresses)

AS

source_addresses(address_label, addressline1,

addressline2, city, region, country, zipcode,

is_deleted)

ON

(

current_addresses.address_label = source_address

es.address_label

)

WHEN NOT MATCHED THEN

INSERT (address_label, addressline1, addressline2,

city, region, country, zipcode)

VALUES (source_addresses.address_label,

source_addresses.addressline1,

source_addresses.addressline2,

source_addresses.city, source_addresses.region,

source_addresses.country, source_addresses.zipcode)

WHEN MATCHED AND source_addresses.is_deleted

= 1

THEN DELETE

WHEN MATCHED THEN

UPDATE

SET address_label=source_addresses.address_label,

addressline1=source_addresses.addressline1,

addressline2=source_addresses.addressline2,

city=source_addresses.city, region=source_address

es.region, country=source_addresses.country,zip

code=source_addresses.zipcode

The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and existing data. Finally, you have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED or delete WHEN MATCHED and some other value indicates delete. This is a real time saver, and the syntax is much cleaner that the older alternative of using homegrown logic

How to export database diagram to other database?

Find system table Dbo.sysdiagrams.
From use insert statement to you database Dbo.sysdiagrams using Identity ON / OFF to column Diagram_id.

Or creating test diagram in database & use update statement and update record from source database.

It will display same diagram as source.

SQL Server Reporting Services 2005 "Error "

ERROR Message
====================================================================
An error has occurred because a control with id 'grdComplaints$ctl04$ctl00' could not be located or a different control is assigned to the same ID after postback. If the ID is not assigned, explicitly set the ID property of controls that raise postback events to avoid this error.

Fiest time report will display good.. but refresh/any post back event.. it throw error...

Try :
1) "EnableViewState" property of GridView was False. When I set it to True. still not works

2) Disable all State porpery to false - Still not work
3) Finally I tried from other browsers I works fine ... I still fining what different setting in my browser than others.

----or---

Change the security setting ...
Unchecked Anonymous user from IIS. Then you won't get any issue... but when you are using Anonymous user. you will get this error.. then follow as before.

"Server Application Unavailable" IIS 6

Running multiple versions of the Framework in ASP.NET
seeprate the application pools according to different vesions of .Net framewrok with application.

Microsoft has done a great job of allowing multiple versions of the framework to run side by side. Version v1.0, v1.1 and v2.0 can all run together at the same time on the same server. Each site, or even a vdir (Virtual Directory) within a site can have its own version of the framework installed. Simply install the framework version on your server and it will be available to you. The install itself is quite small, for example the v2.0 download is 22MB.

The Microsoft homepage for the .NET Framework is:
http://msdn.microsoft.com/netframework/downloads/updates/default.aspx

There are a couple gotchas to consider with running multiple versions of the framework side by side. First, let's dig into IIS a bit. Following is a snapshot of Windows Task Manager on an IIS5 (Windows 2000) server:



Notice the 3 processes called aspnet_wp.exe. There is one per version of the framework. (v1.0, v1.1 and v2.0) If a process for a particular version of the framework doesn't exist, as soon as it's needed, a new process will be spun up. This allows multiple versions of the framework to live beside each other in IIS5. No effort, no pain . . . it just works.

Now consider the following IIS6 (Windows Server 2003) screenshot:



Notice that there aren't any aspnet_wp.exe processes anymore, but there are w3wp.exe processes instead. IIS6 was an impressive upgrade that brought with it some new concepts. One key new concept is Application Pools. A system administrator is able to create groups of sites and place each site in its own group. Whenever a site needs to run, a w3wp.exe process will start for its application pool if it hasn't already started. This brings with it a number of welcome security, performance and management advantages. You are now able to specify your own Identity User which can be unique per Application Pool.

In IIS6, the aspnet_wp.exe process is done away with, and the work that it did is now done within each w3wp.exe process. This has the same advantages I mentioned previously, but it has one big gotcha!

You cannot run more than one version of the framework in the same application pool in IIS6.

While multiple versions of the framework can co-exist on the same server, they can't co-exist in the same process. If you attempt to run multiple versions of the framework at the same time in the same process, the 2nd version that tries to run will fail with the following error:

Server Application Unavailable
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.


You will also receive Event ID 1062 in Event Viewer that says:

"It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."

What to do

Fortunately, the solution is easy enough. Simply create a new application pool and move the site that you will be upgrading to that pool. You can even base it off of the existing one if you don't have the password memorized for the existing one. This is all done within IIS. Once you have placed the site or vdir in its own application pool, then you are ready to upgrade to the new framework version.

I'll cover the different ways to move between different versions of the framework in another blog within the next few days, but the key thing to walk away with now is that multiple versions of the framework cannot co-exist in the same worker process at the same time. IIS5 didn't have any issue with this, but IIS6 requires that each version be in its own app pool.

Refrence : http://weblogs.asp.net/owscott/archive/2006/01/26/436607.aspx

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Whitespace is not allowed at this location.'.
Msg 8179, Level 16, State 5, Procedure XXXXX, Line 45
Could not find prepared statement with handle 15.
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

Whe you parsing xml document, xml tag string contain invalide character li ",&,use

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3576805&SiteID=1

XmlReaderSettings readerSetttings = new XmlReaderSettings();
readerSetttings.CheckCharacters = false;

best decribe
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723005&SiteID=1

itunes.msi not found when tryinh to unistall Itune

when trying to update or unistall Itunes on windows, it complains about a missing Itunes.msi file. If you Browse for this file however, you’ll find it in the location that it’s supposed to be (C:\Program Files\Apple Software Update\Packages).
I tried a few things to fix this, including downloading the latest iTunes from Apple’s website. After about 30 minutes of digging, I discovered that there’s a simple fix.
1) Download the Windows Installer Cleanup Utility
2) Install the program (MS Office not required!)
3) Run the program from Start->All Programs->Windows Install Cleanup Utility
or run msicuu2.exe
4) Click on iTunes, and then click the “Remove” button -( if again same error comes just ingnor & go next step )
5 ) Run the iTunes installer
All in all the process took about 2 minutes, and once again download and listen favorite podcasts

Reporting Services 2005 Dynamic connection string

For report Shared data set use
="Data Source=" & Parameters!server.Value & ";Initial Catalog=" & Parameters!database.Value

For stand alone dataset
Data Source=PARAMETER!server.value;Initial Catalog=PARAMETER!database.value
Some Bugs in Reporting Service 2005
- Make Sure everytime you change anything or open report that Command type is Store procedure or Text . If you use Store procedure , It will change to text.
- If you use a Credential (User Id & password) even standard user ID & pass. when open reports from different user account It will go away. so, when you diploy report make sure Credential are there.

MS SQL (7/2000) database "Suspect Mode" / "Emergency Mode" / "Error 1813

Recovering MSSQL from "Suspect Mode" / "Emergency Mode" / "Error 1813"

For SQL 2000 database some time database goes into suspect mode, when you trying to delete database directly.


sp_configure "Allow updates" , 1
go
update sysdatabases
set status = status & ~ 256
where (name = 'SuspectDatabaseName')
go
sp_configure "Allow updates" , 0
go

* user must be SQL admin user.
it will restore database.

if still not works go for hotfix

click here http://support.microsoft.com/kb/889266

try this http://www.gerixsoft.com/node/24
(good post)

keep in mind

The issues that are fixed by this hotfix package.
The prerequisites for installing the hotfix package.
Whether you must restart your computer after you install the hotfix package.
The files that are contained in the hotfix package।


"Suspect Mode" --> "Emergency Mode"
Open Enterprise Manager,
Right click onto your server node, - an SQL Server Properties dialog will popup,
Enable "Allow modifications to be made directly to the system catalogs" option
Now proceed to the sysdatabases table in the master database, locate your database row and put 32768 into its status column
Restart SQL Server and refresh Enterprise Manager
Open Enterprise Manager - the database will be marked as "Emergency Mode"
These articles explain the trick behind suspect and emergency mode:http://support.microsoft.com/support/kb/articles/Q165/9/18.ASPhttp://www.swynk.com/friends/knight/unmarksuspect.asphttp://support.microsoft.com/support/kb/articles/Q180/5/00.asp
3. "Emergency Mode" --> "Normal Mode"
Now your database is in "emergency mode". Disregard Enterprise Manager does not show tables, views and procedures - use Query Analyzer's Object Browser instead.
"Emergency mode" means database is locked against any modifications, it is readonly forever. There is no way to bring "emergency" database back to normal state. All we can do is copy all data from it into new database.
Create new database (pubz)
Create new DTS package
Use "Copy SQL Objects" task to transfer data from old database to new database (pubs->pubz)
Execute the DTS job & enjoy!
Note that there might be complex dependencies between database objects, so Copy SQL Objects task might not work properly. For example: view A is dependent on view Z; Copy SQL Objects task will try to create view A first, -- that will fail, because view Z does not exist yet.
4. "Error 1813" --> "Suspect Mode"
If you receive "Error 1813", this probably means that you are trying to attach database you have improperly detached, or haven't detached at all. Only successfully detached databases can be successfully attached back.
Create new database using the same name and file location as database that is failing to attach
Stop SQL Server
Copy non-attachable data files over the new ones
Delete log files
Start SQL Server
Open Enterprise Manager - the database will be marked as "suspect"
Once your database is "suspect", use instructions outlined above...
Note that database will be marked "suspect" only if transaction log contained active sections. To illustrate the problem for 100%, run a few inserts against the database before deleting transaction log.
http://www.gerixsoft.com/trackback/24

SQL find out two date between intervals

select getdate()
select dateadd("yy",-1,getdate())


dd - day
mm - month
yy - year

-1 - add -1 day/month/year in gurrent date.