Adding UNICODE data (Urdu/Arabic or RTL data) into Database

Problem:

Adding unicode data to the database produces weird question marks '????? ???? ??????' on output or displaying data.

Solution:

We can very easily correct this from the query analyzer using the "N" prefix, which instructs SQL server to treat this data as unicode. For example, the following code works perfectly.

sample query

update tblUrduArticles
set UrduDesc = N'بول کہ لب آزاد ھیں تیرے'
where ArticleID = 1296

I've checked it sql server 2000 successfully, hopefully it will work for you as well.

Regards

Imran Saami

Changing Ownership of Database objects

change ownership of a single object

EXEC sp_changeobjectowner 'authors', 'Muhammed'

 

Bulk Table Change Owner:

This will change the ownership of all tables found under a specified owner

DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)
SELECT

@oldOwner = 'aspalliance'
, @newOwner = 'dbo'
, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @oldOwner + '''
)

EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''
EXECUTE sp_MSforeachtable @sql

 

 

Bulk Stored Procedure Change Owner

Since there's no direct iteration method for stored procedures so the code below will produce the queries for each stored procedure, copy the produced text and run it in query analyzer.

DECLARE @oldOwner sysname, @newOwner sysname

SELECT @oldOwner = 'aspalliance' , @newOwner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion'

Exception Details:

System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'g4ity_dbase4ity', owner 'dbo'.

OR

ANY kind of Execute Permission Denied on sql Server object.

Solution

• Goto properties of the dbo.aspnet_StoredProcedureName
• Open Permissions
• Allow your username for exec Permission

Overview

Whilst SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will). This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005

SQL2000

A common answer to the question posed by the title of this article is to run a query such as the one below in Query Analyzer and copy and paste the results into a query window and execute them. The query uses the INFORMATION_SCHEMA views to generate a list of GRANT statements for each procedure in the database.

Do one of the following

1. Goto properties of the dbo.aspnet_StoredProcedure Name Open PermissionsAnd Allow your username for exec Permission
   .
2. To change permissions on an individual object
   grant exec on [Owner].[ObjectName] TO UserName
   .
3. Simply run in sql Query Analyzer to change permissions on all objects in a database. It will generate a resultset with GRANT permission queries for all possible objects. copy it and paste in the query window of query analyzer and run it.
   SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINES
   WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
   .
4. Run this code if you want to change permissions on objects under dbo's ownership. Run this code same as mentioned in part 2 above.
   SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +QUOTENAME(ROUTINE_NAME) + ' TO ' FROM INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 AND QUOTENAME(ROUTINE_SCHEMA)='[dbo]'

SQL2005

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

DotNetNuke Error: URL blinks several times in status bar and doesn't browse the site :'(

PROBLEM:
The address blinks many times in the status bar and finally browser shows the 'Page Cannot be displayed' error and sometimes this blinking loop never ends.

EXPLANATION
It normally happens when you change the alias of the portal in 'portalalias' table.

SOLUTION :

1. Make sure the alias in the portalalias table is correct OR
2. Restart the site from IIS. If you don't have rights to do that u can just make a fake change in web.config and upload it again. The site will be restarted. 
3. If site is running on local host then simply kill the aspnet_wp.exe processe
4. Browse the site again,

IF problem persists, then get help from your host, there must be some issues in NS servers.

 Cheers

Parser error in ASP.NET 2.0 web project

Parser Error
Description:

An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Stream is not a valid resource file.

Source Error:

Line 1:  <?xml version="1.0" encoding="utf-8" ?>
Line 2:  <locales>
Line 3:      <inactive>    

Source File: /App_GlobalResources/Locales.Portal.xml.resources Line: 1

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

EXPLANATION

This error comes usually when you try to run a .NET2.0 site with .NET1.1 and vice versa.

SOLUTION

• go to the site properties in IIS
• Open ASP.NET tab.
• Just switch the ASP.NET version 2.0 to ver 1.1