SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

\ by

Script to list all the schemas for a database and then construct the code to change it to [dbo].

See here for more information.

USE MyDatabase
go

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata

DECLARE @vcUser VARCHAR(128),
    @vcSQL VARCHAR(8000)
SELECT  @vcUser = 'any non dbo username causing the problem'

IF EXISTS ( SELECT  s.name
            FROM    sys.schemas s
            WHERE   s.principal_id = USER_ID(@vcUser) )

-- Now replace the result name in following script:
    SELECT  @vcSQL = 'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(s.name) + ' TO dbo;'
    FROM    sys.schemas s
    WHERE   s.principal_id = USER_ID(@vcUser)
            
PRINT @vcSQL
IF 1 = 1
    EXEC (@vcSQL)
    

-- Get the associated schema
SELECT  *
FROM    information_schema.schemata
Posted in: SQL Server

Windows Server settings Background Services and using for SQL Server

\ by

Here are some related discussions.

 

1) Here’s a link for a discussion about the setting.

If the value was set to 0 then that means that the Foreground and background applications equally responsive. I tested it in my LAB. on my system default value was set to 2 and when changed to Background services registry value changed decimal 24. Sometime it may happen that the settings you configure via GUI doesn’t take effect properly thus registry can edited directly. If you have value 24 in registry then your system is optimized for Background Services. No need to worry then.

Look at this section below from- http://support.microsoft.com/kb/102987

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

PriorityControl Entries

The PriorityControl key defines the foreground/background priority boost differential. Change this value by choosing the Tasking button in the System dialog box in Control Panel. Values are stored under this Registry path:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\PriorityControl

Win32PrioritySeparation REG_DWORD 0, 1, or 2

Default:   0x2

Specifies the priority to give to the application running in the forground. This application receives more process or time relative to other applications running in the background. The values here correlate to the following options in the Tasking dialog box:

Value Meaning

0          Foreground and background applications equally responsive
1          Foreground application more reponsive than background
2          Best foreground application response time

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

Cheers!

Sachin Gadhave
MCP, MCSA, MCTS 

 

2) And here’s another one

 For SQL Server you can choose Background Services. But there a lot of more setting you need to configure:

  • To enable SQL Server to use large pages, enable the Lock pages in memory user right assignment for the account that will run the SQL Server: From the Group Policy MMC snap-in (Gpedit.msc), navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment. Double-click Lock pages in memory and add the accounts that have credentials to run SQL Server.
  • You can set a fixed amount of memory for the SQL Server process to use. About 3% of the total available memory is used for the system, and another 1% is used for memory management structures. SQL Server can use the rest of available memory, but not more. The following equation is available to calculate total memory to be used by SQL Server: TotalMemory – (1%memory * (numa_nodes)) – 3%memory – 1GB memory
  • Set CPU affinity for the SQL process: Set affinity mask to partition the SQL process on specific cores. To set affinity on more than 32 logical processors, use affinity64 mask. Starting with SQL Server 2008 R2, you can apply equivalent settings for configuring CPU affinity on as many as 256 logical processors using the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY Data Definition Language (DDL) TSQL statement as the sp_configure affinity mask options are announced for deprecation. Use the ‘alter server configuration set process affinity cpu =’ command to set affinity to the desired range of processors for each k-group, separated by comma.

This are just a few settings, in this whitepaper you will find more settings you need to configure (choose the ones that you find relevant): Performance Tuning Guidelines for Windows Server 2008 R2

Posted in: SQL Server, Windows

Install 64-bit ACE.OLEDB Download Microsoft Access Database Engine 2010 with 32-bit Office

\ by

 

  1. Download Microsoft Access Database Engine 2010 Redistributable here
  2. Install the version you need. I have 32-bit Office and a 64-bit PC using Windows 7 so had to do this to avoid the error about uninstalling Office:
  3. AccessDatabaseEngine_X64.exe /passive
  4. And it worked!

EDIT:

But it also now creates a problem whenever an OFFICE document is opened. Here’s a solution, although I had to add the “NoReReg REG_DWORD 1” myself in HKCU\Software\Microsoft\Office\14.0\Word\Options
Microsoft Office 2010 Professional Plus configures each time you start 

Posted in: PowerShell, SQL Server, Windows

Msg 7415, Level 16, State 1, Line 1. Ad hoc access to OLE DB provider ‘ MSDASQL’ has been denied.

\ by

Unless you are an ‘sa’ you probably will get this error.

Fix it by adding this setting to the registry.

Value Data
DisallowAdHocAccess 0

 

—
— Quickly check your registry settings running this on SQL 2005+ servers

EXEC master..xp_regenumvalues 
                        ‘HKEY_LOCAL_MACHINE’,
                        ‘SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Providers\MSDASQL’

— or on SQL 2000

EXEC master..xp_regenumvalues 
                        ‘HKEY_LOCAL_MACHINE’,
                        ‘SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL\’

Posted in: ERRORs, SQL Server

How to remove Reporting Services from SQLServer 2008 R2

\ by

1.In Add/Remove programs
2.Click the ‘Uninstall/Change’ button for “SQL Server 2008 R2”
3.In the coming dialog, click ‘Remove’
4.Click ‘OK’
5.In the ‘Select Instance’ dialog,  select the appropriate instance of the SQL Server (!) and then click ‘Next’
6.In the ‘Select Features’ dialog, please only select ‘Reporting Services’
7.Then click ‘Next’ and ‘Next’ till the uninstalling process starting

Step 6

Select Features

Step 7
Removal Rules

Ready to Remove

Posted in: SQL Server

PowerShell and why I will learn it.

\ by

Up until SQL Server 2008, I didn't need PowerShell to manage my dozen servers and know exactly what was going on. Now I do.

And the benefits and capabilities are plentiful! You can start to be convinced by reading this article, "Why This SQL Server DBA is Learning Powershell", brilliantly conceived.

Next, use this site as a resource and examples.

Here is a great set of examples by MAK.

Posted in: PowerShell, SQL Server

Granting ASP.NET web application access to run procedures in SQL

\ by

USE MyDatabase
GO

— Windows 2000 / XP
— Replace "{servername}" with your SQL Server name

EXEC sp_grantlogin [{servername}\ASPNET] 
EXEC sp_grantdbaccess [{servername}\ASPNET], [NETAPP]

— GRANT EXECUTE ON [ProcedureName] TO [NETAPP]
grant exec on [dbo].[usp_My_Procedure] TO NETAPP 
GO

— Windows Server 2003
— Info.

EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

Posted in: SQL Server, WEB

[ODBC Driver Manager] Driver does not support this function … ‘MSDASQL’ IDBInitialize::Initialize

\ by
On a new SQL Server I was trying to run an OPENROWSET query that opened a Foxpro free table and was getting this error. It turned out the latest Foxpro drivers were not yet installed. An easy way to find the drivers and their dates is to open the ODBC Data Source Administrator and click on drivers. You could also test by trying to create an ODBC connection to Foxpro.
 
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].
 
e.g. 

SELECT *   
FROM  OPENROWSET (
  'MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=E:\DATA;SourceType=DBF;',
  'SELECT  * FROM MyFreeTable;'
)

Once the correct drivers were installed we could run the query, above, and create ODBC connections. Then, we were getting this error, but only when not running directly on the server itself.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hprofile.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

So, our OPENROWSET queries and the linked server queries could be run locally to the server. There must still be some proxy permissions or security something we still need to set. 

Still, another error we're getting is this: 
Cannot get the schema rowset 'DBSCHEMA_CATALOGS' for OLE DB provider "SQL Server" for linked server "(null)" — huh?

Posted in: SQL, SQL Server