vrijdag 16 mei 2008
[Excel Source ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".
Cause: There is no 64 bit driver for excel. The workaround is to run the package using the 32 bit version of the execution utility.
Solution: Project Properties->Degugging Section, I set Run64bit RunTime to False.
dinsdag 6 mei 2008
The "strange" thing is that the server net libraries remain the original service pack versions, wich can be confusing.
I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )
EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
EXEC sp_MSforeachdb @command
vrijdag 25 april 2008
Msg 7340, Level 16, State 4, Line 1
Cannot create a column accessor for OLE DB provider "IBMDA400" for linked server "ATCOAIRC".
- Browse in the Management Studio to the linked Server, request the properties, and close this window again everytime SQLServer Service starts ;-)
Or, if you don't want to do this manually - Duhhh...
- USE master;
EXEC sp_configure 'show advanced option', '1';
EXEC sp_configure 'scan for startup procs', '1';
CREATE PROC usp_enum_oledb_providers
exec sp_procoption N'usp_enum_oledb_providers', 'startup', 'on'
Note: I didn't have this problem with SQLServer or Oracle Linked Servers.
vrijdag 11 april 2008
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "IBMDA400" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "IBMDA400" for linked server "(null)".
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
--> An exception occured while executing a Transact statement or batch.
--> Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider "IBMDA400" for linked server.
1) Make sure on your SQL Server 2005 x64 (64 bit) machine that "Allow inprocess" is checked for the linked server provider (IBMDASQL in my case).
--> In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Server->Providers in the Object Explorer.
--> Right-click on IBMDASQL and select Properties. Check the "Allow inprocess" box in the Provider Options dialog that comes up.
2) Enable Ole Automation Procedures;
sp_configure 'show advanced options', 1;
sp_configure 'Ole Automation Procedures', 1;
vrijdag 4 april 2008
A. Listing effective permissions on the server
The following example returns a list of the effective permissions of the caller on the server.
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
B. Listing effective permissions on the database
The following example returns a list of the effective permissions of the caller on the AdventureWorks database.
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
C. Listing effective permissions on a view
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')
ORDER BY subentity_name, permission_name ;
D. Listing effective permissions of another user
The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on user Wanida.
EXECUTE AS USER = 'Wanida';
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
E. Listing effective permissions on a certificate
The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.
SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');
F. Listing effective permissions on an XML Schema Collection
The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in the AdventureWorks database.
SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',
'XML SCHEMA COLLECTION');
G. Listing effective permissions on a database user
The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.
SELECT * FROM fn_my_permissions('MalikAr', 'USER');
H. Listing effective permissions of another login
The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.
EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT * FROM fn_my_permissions('AdventureWorks.HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
vrijdag 28 maart 2008
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
donderdag 27 maart 2008
SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?
One resolution is to add a switch to the Management Studio shortcut as defined below. Access the below screen by going to Start > Programs > SQL Server 2005 program group > 'SQL Server Management Studio' program, right-clicking will get you the following screen by opening the General tab, then select shortcut tab to add 'nosplash' switch:
Also the user needs to tweak the Internet Explorer security settings as follows:
Uncheck two options in Internet Explorer's Advanced Security settings. Namely:
"Check for publisher's certificate revocation"
"Check for server certificate revocation".
Both these steps combined should improve the load time of SQL Server Management Studio.
woensdag 26 maart 2008
Before any debugging can take place, you first must create the store procedure. This can be done either by using Management Studio/New Query or by building the procedure graphically in Visual Studio 2005. Stored procedures can be created in Visual Studio by opening a new database project and using the Visual Studio Installed Templates.
Either way, once the procedure exists, you are ready to start debugging from Visual Studio 2005. When you start up Visual Studio for debugging purposes, you don't have to create a project. Instead, you can create a connection to your database from Server Explorer (You will have to provide your server name along with your login credentials and choose the AdventureWorks database.) Then you can drill down and open up the stored procedures folder. Next, right click on the procedure you wish to debug and choose Step Into Stored Procedure from the context menu. Then you are ready to start debugging!
Note: Before you can start "Remote debugging", you have to install "msvsmon.exe" from the visual studio CD or the sqlserver directory "C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe".
Micorosft development team is looking to implement a better way within SQL Server 2008 and as of now 2005 is only way out with Visual Studio.
Moving MSDB is more complicated than other system databases and requires you to add trace flags and parameters to the SQL Server startup to detach msdb, then remove them and restart SQL Server to attach it:
Start SQL Server with -c, -m and -T3608 and then perform a detach. You would then remove these parameters before attaching MSDB.
vrijdag 21 maart 2008
Secondly you need to address the model database, because tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful.
The last one you have to re-address is the msdb, because msdb is used by the SQL Server agent and has no bearing on the stability of the database engine.
donderdag 20 maart 2008
Given a tree such as this:
2 3 4
/ \ / \ \
5 6 7 8 9
a traversal of nodes in 1-2-3-4-5 order would be a breadth-first traversal.