Action plans and problem solving

Parameter gets converted in execution plan, when you explicitly define a different datatype for the parameter

Consider this scenario:

You are trying to identify why a query like this is performing poorly:

UPDATE table SET column1 = 0 WHERE table .column2 = @Parameter

Where the @Parameter is explicitly specified as datatype VARCHAR(10) and the column2 is defined as CHAR(10) earlier in your transaction.

There is a simple explanation why this query is performing poorly:

-       You explicitly specify the data type for the @Parameter as a different data type than the column with which you compare it.

-       The SQL Server optimizer by default assumes you picked the different data type intentionally.

-       So SQL Server converts EACH value of column2 to the same data type as the @Parameter.

This behavior can be reproduced in SQL Server 2000, 2005 and 2008.

However there is a workaround for this issue, to rewrite the query like this:

UPDATE table SET column1 = 0 WHERE table .column2 = CONVERT(varchar(10),@Parameter)

This way you can convert @Parameter before comparing, by using an explicit conversion for the parameter with the correct data type.

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Msg 7399 – OLE DB provider “MSDASQL” - The provider reported an unexpected catastrophic failure

Consider this scenario:

-       You have setup a linked server by using the MSDASQL provider

-       You are sometimes getting this error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "XXXXXX" reported an error.

The provider reported an unexpected catastrophic failure.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "XXXXXX".

-       Every time you get this error, the SQL Server has to be restarted for the linked server to be operational again.

After having troubleshooted the above problem we found by analyzing the SQL Server minidump files that were produced that the issue was caused by a 3^rd party DLL. There was an access violation of a call from pgoe1023.dll in this case.

This issue is also discussed in this newsgroup:

Exception Access Violation in SQL

http://www.sqlnewsgroups.net/sqlserver/t25379-exception-access-violation-sql.aspx

We tried as a workaround to run this DLL outside of the SQL Server process:

-       You could do this test and potentially instantiate the DLL outside SQL Server process by changing the "Allow in Process" provider property.

-       This will ensure that even if the Fatal exception occurs, it will not cause SQL Server to stop. The .dlls will be running external to SQL Server in a process called DLLHost.exe, and the calls will be marshaled back and forth between the two executables to perform the work, so there is a slight performance overhead which you should test thoroughly.

-       However some 3rd party providers cannot run outside of the SQL process, so the “In Process” option has to be used for those. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.

However this particular DLL could not execute outside the SQL server process. So the 3^rd party provider vendor was contacted who reproduced the issue in-house and who would provide a fix for this issue in some future version of the provider.

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

No collector registered for scope: "SetupStateScope” - Message pump returning: 1602

Consider this scenario:

-       You have already installed a SQL Server 2000 instance on a Windows 2003 machine.

-       You also try to install a SQL Server 2005 instance on the same Windows 2003 machine.

-       Searching in the hotfix.log and summary.txt log files you see that this error is responsible for the failure:

Failed to find property "watsonFailedActionErrorCode" {"SetupStateScope", "", ""} in cache

              Source File Name: datastore\propertycollection.cpp

            Compiler Timestamp: Wed Jun 14 16:28:01 2006

                 Function Name: SetupStateScope.watsonFailedActionErrorCode

            Source Line Number: 44

            ----------------------------------------------------------

            No collector registered for scope: "SetupStateScope"

Failed to create CAB file due to datastore exception

  Source File Name: datastore\cachedpropertycollection.cpp

Compiler Timestamp: Wed Jun 14 16:27:59 2006

     Function Name: CachedPropertyCollection::findProperty

Source Line Number: 130

----------------------------------------------------------

Failed to find property "watsonFailedActionErrorCode" {"SetupStateScope", "", ""} in cache

              Source File Name: datastore\propertycollection.cpp

            Compiler Timestamp: Wed Jun 14 16:28:01 2006

                 Function Name: SetupStateScope.watsonFailedActionErrorCode

            Source Line Number: 44

            ----------------------------------------------------------

            No collector registered for scope: "SetupStateScope"

Message pump returning: 1602

This error usually means that some components of SQL Server 2005 are already installed on the machine and this is probably why the setup fails.

Particularly we checked if these components were installed:

-       Microsoft office 2003 web components

-       Microsoft SQL Server 2005

-       Microsoft SQL Server 2005 Backward compatibility

-       Microsoft SQL Server native client

-       Microsoft SQL Server setup support files

-       Microsoft SQL Server VSS writer

We uninstalled any of these components that were already installed. Then when we executed the setup again, the operation went smoothly.

We couldn’t however find out how some of these components were already installed prior to installing SQL Server 2005.

Disclaimer

 This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Request from IIS 5.0 arrives as anonymous login (‘null’) on SQL Server 2000

Recently we did troubleshoot an issue where requests from an IIS 5.0 site arrived as anonymous logins (‘null’) in SQL Server 2000.

The ASP.NET application was using IIS 5.0 anonymous authentication for its users to send requests to SQL server 2000. The IIS 5.0 was allowing anonymous authentication and each anonymous user connection was configured in IIS settings to be impersonated by a domain account. Also the “Integrated Windows Authentication” option in his IIS "Directory Security" tab.

At first we thought this was a typical Kerberos issue, but we were also able to reproduce the issue without having a double hop (e.g. client machine -> IIS -> SQL server).

But later on we discovered these facts about IIS 5.0 (with which I never had experience in the past as it is a quite old product J):

Ø  ASP.NET is not impersonating the user by default. This means that the process account will be used for authentication. For IIS 5 this is the local system and therefore we cannot pass the credentials to SQL Server.

Ø  One solution for ASP.NET is to either impersonate the anonymous domain account directly in the web.config.

Ø  Another solution is to impersonate the logged on user.

Ø  However both these solutions require that you supply the password of the account and so it might be easier to just use SQL authentication instead of Windows authentication.

After also reviewing the application’s source code it was clear that with windows authentication this scenario would not work. However since what we wanted was to achieve was that the database connection was always established under a certain identity and also to execute every request under a specific identity as well, there was a workaround:

1.    Run the worker process under the desired identity. To do so you can modify your machine.config. The <processModel> element offers two attributes "userName" and "password" where you can enter the identity under which the aspnet_wp.exe would run.

For reference:

processModel Element (ASP.NET Settings Schema)

http://msdn.microsoft.com/en-us/library/7w2sway1(VS.80).aspx

2.    Impersonate every request to a specific identity. This can be achieved by adding the identity element to the asp.net application’s web.config.

e.g.

<identity impersonate="true" userName="domain\user" password="*******"/>

For reference:

identity Element (ASP.NET Settings Schema)

http://msdn.microsoft.com/en-us/library/72wdk8cc(VS.80).aspx

3.    Use SQL Server authentication to connect by providing the credentials to access the DB in the ado.net connection string.

e.g.

"Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=AdventureWorks;Server=MySqlServer"

For reference:

Connection String Syntax (ADO.NET)

http://msdn.microsoft.com/en-us/library/ms254500.aspx

Disclaimer

 This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

The SQL server specified in SSIS service configuration is not present or is not available

Consider this scenario:

-       You have setup a SQL Server 2005 failover instance.

-       You then install SSIS 2005.

-       When you try to access the MSDB database from inside the Integration Services you get this message:

The SQL server specified in SSIS service configuration is not present or is not available

The explanation is that you need to configure SSIS to access the correct SQL Server:

v  For this purpose SSIS uses a file called MsDtsSrvr.ini.xml to determine on which SQL Server the SSIS will connect to.

v  So open this xml file (e.g. by using notepad) and find these tags:

<ServerName>XXXXXXXXX</ServerName>

v  Here you need to replace the value XXXXXXXXX with the name of the SQL Server that you want SSIS to connect to.

e.g.

for a local default instance of SQL Server you can use this value:

<ServerName>.</ServerName>

for a named instance, this would look like:

<ServerName>mySQLServer\mySQLInstance</ServerName>

for a default failover instance you would use:

<ServerName>myVirtualSQLServername</ServerName>

and finally for a named failover instance you would use:

<ServerName>myVirtualSQLServername\ myVirtualSQLInstance</ServerName>

As a best practice, restart the SSIS service and then connect again to SSIS. The error should not reappear.

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Test connection failed because of an error in initializing provider (ORA-12154)

Consider this scenario:

-       You have migrated a DTS packages from SQL Server 2000 to SQL Server 2005.

-       The new SQL Server is installed on a Windows 64-bit machine.

-       This DTS package is connecting to an Oracle 10G database.

-       You get this error or something similar:

Test connection failed because of an error in initializing provider (ORA-12154)

This Oracle specific error (ORA-12154) is usually caused by the parenthesis (x86) in the Program Files of the 64-bit Windows.

To resolve this error, it is recommended that you contact Oracle, who will provide you with a fix for this abnormal behavior (Oracle bug #3807408).

But even if you don’t receive the fix from Oracle, you could use this workaround (though it is obviously easier to have the fix applied than doing all these steps below J ):

1. Copy Folder Oracle32bit and Oracle64bit - These folders have the drivers and the required upgrades

2. Install 10.2.0.1 client for 32bit - Select Administrator option

3. Install 10.2.0.2 upgrade for 32bit - You need to select the correct Oracle Home path of previous 10.2.0.1 install.

This install might fail at copying gacutil.exe. To Fix this search for gacutil* in C drive and you will find gacutil.exe anc gacutil.exe.config files in some directory. Copy them to C:\Program Files (x86)\Microsoft Visual Studio

8\SDK\v2.0\Bin. Sometimes you can't find gacutil.exe so then you may copy this file from some other server. Rerun the previous install after copying gacutil files - Install will be successful.

5. To install the patch for 10.2.0.2

Create folder Patches in D:\oracle\product\10.2.0\client_1

Copy folder 5383042 from

D:\Oracle32bit\Patchfor10202\p5383042_10202_WINNT TO

D:\oracle\product\10.2.0\client_1\Patches

Go to command prompt

cd D:\oracle\product\10.2.0\client_1

set oracle_home=D:\oracle\product\10.2.0\client_1

cd D:\oracle\product\10.2.0\client_1\Patches\5383042>

D:\oracle\product\10.2.0\client_1\OPatch\opatch apply

The above command calls opatch.bat and you are in the patch folder so "apply" will apply the current patch folder you are in. It will prompt you for y/n ...say y

6. Install 64 bit 10.2.0.1 client for 64 bit - Make sure you are not overwriting the 32 bit Oracle Home; Select Administrator option

7. Install 64bit 10.2.0.2 upgrade - Select the correct Oracle Home.

8. Install 64bit 10.2.0.3 upgrade - Select the correct Oracle Home.

9. Copy TNSNAMES folder from other servers to D drive.

10. Create an Environment variable TNS_ADMIN to path D:\TNSNAMES

Notes:

-       After following above steps, the SSIS package would run from BIDS

-       However, it will fail as a SQL JOB if 64 BIT Oracle Client is not there as by default in x64 environment, the 64 BIT DTEXEC.exe would be invoked

-       Therefore, we need 32 BIT Oracle Client to run package from BIDS (32-BIT) as well as 64 BIT Oracle Client to run from Sql Job (64-BIT DTEXEC)

-       If we DO NOT want to install 64-BIT Oracle client, we need to modify the following registry key to invoke 32-BIT DTEXEC.exe from a 64-BIT SQl

-       HKLM->SOFTWARE->MICROSOFT->MSDTS->SETUP->DTSPATH : Change the value of the DEFAULT key to point to the 32-BIT DTEXEC.exe. Typically, under default installation, that path will be:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS

-       After this, the job should run successfully as well

 Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Installing SSIS services after installing SQL Server database engine

Consider this scenario:

-       You have installed SQL Server 2005 and patched it with a Service Pack (in our case it was SP3).

-       Then you install SSIS on the same box, but do not patch it with any Service Pack.

-       Specifically you get this error when you try to start the SSIS service:

Time:     23/9/2009 10:05:34 πμ

ID:       7000

Level:    Error

Provider: Service Control Manager

Machine:  PRODSQL11

Message:  The SQL Server Integration Services service failed to start due to the following error:  %%1053

Time:     23/9/2009 10:05:34 πμ

ID:       7009

Level:    Error

Provider: Service Control Manager

Machine:  PRODSQL11

Message:  A timeout was reached (30000 milliseconds) while waiting for the SQL Server Integration Services service to connect.

There seemed to be two possible causes for this behavior:

1. You need to install the appropriate service pack and choose to patch SSIS. Apparently there is a known issue that causes the service timeout if the SSIS service is unpatched, while database engine is already patched.

2. The .NET framework is damaged and needs to be repaired from Add/Remove programs.

In our scenario, method 1 resolved this error.

Also note that SSIS needs to be patched to be in supported configuration (with at least SP2 at the time of writing this blog post), so never install SSIS and leave it unpatched in RTM.

 Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Error with event id of 17052 reports Access Violation

Now this is one was a thrilling issue! Consider the following scenario:

You have a SQL 2000 SP4 server running on Windows 2000 SP4 server. After you install some updates, you get an error like this error reported in the event viewer:

Event Id 17052 – Access Violation

If you go to the SQL ERRORLOG you will see an error like that:

Process XX generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION

Also a minidump is created in your LOG folder. Analysis of the minidump’s stack trace relates this error to the Windows 2000 Post-Service Pack 4 COM+ 1.0 Hotfix Rollup Package 32a.

The Distributed Services Support team has blogged very useful information about this issue here:

Access Violation in ole32 dll after installing MS09-012 on Windows 2000 servers

http://blogs.msdn.com/distributedservices/archive/2009/06/25/av-in-ole32-dll-after-installing-ms09-012-on-windows-2000-servers.aspx

In summary, to avoid this issue when using SQL Server 2000 on Windows Server 2000 you will need to have this rollup installed:

Availability of Windows 2000 Post-Service Pack 4 COM+ 1.0 Hotfix Rollup Package 32

http://support.microsoft.com/?id=895177

What is interesting, is that this access violation error is pointing to some SQL Server specific errors if you start researching it, but it is actually related to the ole32.dll file. A truly misleading error...

 Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Error 5042 (000013b2) when trying to failover an Exchange cluster group

  Recently we came across an interesting issue. When trying to failover an exchange cluster group, the operation failed with error :

"The cluster node was not found", Error ID:5042 (000013b2)

This problem has two common causes:

1.    One common cause is that If you have any 3^rd party resource in the exchange group (e.g. the SCANMail resource), which is already present before you add a new node to the cluster, this problem might occur. When a 3^rd party resource like SCANMail is created, then it will only pick the current nodes in the list of "Possible Owner" and it will not allow you to modify the list of "Possible Owner" manually after the resource is created.

So if you have any resource in your exchange cluster group that was not created by the original exchange setup, please delete this 3^rd party resource and recreate it again, so that it picks all the nodes in the list of "Possible Owner". Then you should be able to perform the failover.

2.    Another common cause is that one of the resources that are inside the exchange cluster group may have invalid possible owners. Please check each resource and verify that the possible owners (nodes) are correct for each of these resources.

In our case it was cause #2 that was giving us a hard time.

Note: In order to make the necessary checks and changes, make sure that you understand the difference between Possible Owners and Preferred Owners of a cluster resource and cluster group respectively.

So it is a good idea to to review this blog post that describes the difference: http://blogs.msdn.com/clustering/archive/2008/10/14/9000092.aspx

Specifically note that the Preferred owners configuration corresponds to the Cluster Group level, while the Possible owners corresponds to the Cluster Resource level. If a node is not listed in the Possible Owners of the resource, then this node cannot handle this resource.

Also as specified here: http://technet.microsoft.com/en-us/library/bb123858(EXCHG.65).aspx

-       The nodes that you list as possible owners of a resource, limit where the Exchange Virtual Server can run.

-       If all the resources on an Exchange Virtual Server have the same possible owners, the server can run on any of the listed nodes.

-       If one of the resources cannot list a node, the Exchange Virtual Server cannot run on that node, even if all the remaining resources list the node as a possible owner.

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

How to add an additional node to a SQL Server failover cluster

  Sometimes a two-node cluster configuration is not enough for your SQL Server, as you may want to create new SQL instances, which will reside in a different node than the existing SQL instances. So in this case you may want to add a 3^rd or a 4^th node to the SQL cluster.

Assuming you already have installed a SQL Server failover instance on two nodes and you now want to add a 3^rd node, the steps to perform this procedure are as follows:

1.     Setup Windows on the 3^rd machine.

a.     Patch Windows with the latest Service Pack.

b.     Install the recommended hotfixes for a Windows cluster node (on all nodes).

e.g.       Recommended hotfixes for Windows Server 2003 Service Pack 2-based server clusters

http://support.microsoft.com/kb/935640

2.     Setup the public network of this new node properly, in the same subnet with the other two nodes.

e.g. if the two existing nodes have these IP addresses: 192.168.2.101 & 192.168.2.102, then give an IP address in this range: from 192.168.2.1 to 192.168.2.255

Also setup the private network properly, to match the subnet and IP mask of the other two nodes’ private network.

Note: Setting up the networks of a cluster node are documented in pages 6-12 & 30-32 of this whitepaper: http://www.microsoft.com/downloads/details.aspx?familyid=96F76ED7-9634-4300-9159-89638F4B4EF7&displaylang=en

3.     Join the new node in the same domain with the other two nodes.

4.     Document what shared disks the other two nodes can see. The new node need to have access to these shared disks too.

5.     Logon in one of the other two nodes and open the cluster administrator console. Perform these steps:

a.     Click File, click New, and then click Node.

b.     The Add Cluster Computers Wizard will start. Click Next.

c.     If you are not logged on with appropriate credentials, you will be asked to specify a domain account that has administrative rights over all nodes in the cluster.

d.     Enter the machine name for the node you want to add to the cluster. Click Add and then click Next.

e.     The Setup wizard will perform an analysis of all the nodes to verify that they are configured properly.

f.      Type the password for the account used to start the cluster service.

g.     Review the summary information that is displayed for accuracy. The summary information will be used to configure the other nodes when they join the cluster.

h.     Review any warnings or errors encountered during cluster creation, and then click Next.

i.      Click Finish to complete the installation.

Note: These steps are documented in page 29 of this whitepaper: http://www.microsoft.com/downloads/details.aspx?familyid=96F76ED7-9634-4300-9159-89638F4B4EF7&displaylang=en

Now the third node has been added to the cluster. So now the cluster group should be able to failover properly to the third node as well.

6.     However we still need to configure the SQL Server instances so that they are able to failover to the third node as well. The process for adding or removing a node is similar to setting up a new clustered instance of SQL Server:

a.     Logon to one of the old nodes where SQL server is already installed.

b.     Go to Control Panel and open Add or Remove Programs.

c.     Select Microsoft SQL Server 2005. Choose which SQL instance you will configure. Then click Change.

d.     Setup launches.

e.     At the Change or Remove Instance page, select Maintain the Virtual Server.

f.      Go through the menu pages and in the Cluster Node Configuration page add the third node, e.g. like seen in this screenshot:

g.     All nodes except the node from which you are installing, should be displayed in the Selected nodes list.

You need to perform these steps one time for each instance you have (so in your case this means that you have to add the third node three times, one for each instance).

Note: These steps are documented in page 25 of this whitepaper: http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&displaylang=en

So although this is a delicate procedure, it is not very complex and if you perform this during a scheduled downtime you should not face any strange issues.

Disclaimer

 This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

URL Rewrite Module may not work as expected when redirecting to file://

The URL Rewrite Module is part of the Microsoft Application Request Routing for IIS 7:

Microsoft Application Request Routing Version 1 for IIS 7 (x86) http://www.iis.net/downloads/default.aspx?tabid=34&i=1709&g=6

Maybe you have been trying to use this module in order to redirect to a file:// url. In this case, the module may not work as expected depending on the version of the module.

e.g. consider this scenario

You are trying to use the URL Rewrite module, so that his web site redirects the client to a network share to download the corresponding file.

In order to achieve this you have installed IIS 7 and you have enabled Failed Request Tracking as per article: http://learn.iis.net/page.aspx/467/using-failed-request-tracing-to-trace-rewrite-rules/.

You have also created a network share with a text file in it (for testing purposes you may use a test.txt file).

You then install the URL Rewrite Module and create a new application and point it to an empty local path.

Then you open the URL Rewrite Feature and choose to add a new Rule.

The new rule could be as follows:
- Requested URL: Matches Pattern
- Using: WildCard
- Pattern: *
- Ignore Case: Checked
- Conditions: No Conditions Defined
- Action Type: Redirect
- Redirect URL: ffile://hostname/sharename/directoryname
- Append Query String: unchecked
- Redirect Type: 301

You then save and Enable the Rule and try the rule by navigating to your website/TEST/test.txt

If you hit the same issue, you will see that your URL has been rewritten in the form /VirtualDirectory/file://xxxxxxxx

So if you hit this issue, you may contact the Microsoft Product Support Services, who can provide you with a new version of the Rewrite Module you could use that is not affected by this issue.

As of the writing of this post, I could not find this new version publicly available in the internet.

Disclaimer

 This is a personal weblog. The opinions expressed here represent my own and not those of my employer. The steps are provided “as is” without a warranty of any kind.

Recovery of database 'XXXXXXXXX' (X) is X% complete

Recently I received a question regarding SQL Server database Analysis and Recovery.

It seems that a pretty huge database got into recovering mode, due to a huge transaction that was taking place, when suddenly the disk ran out of space and the transaction was left incomplete. When more free space was added to the disk, SQL Server started rolling back this transaction.

The initial estimation of SQL Server was that this procedure would take more than one full day to be completed, but usually this is a pessimistic view – the procedure usually takes less than the initial estimation.

Under normal circumstances, we should wait for the recovery of the database to finish, so that all open transactions are rolled back successfully. However as this database was important, we were trying to find a workaround to get this database back online ASAP.

So the alternatives were:

1.    Restore the database from a valid backup. Usually a restore from backup takes less time.

2.    Interrupt the recovery process. But then we would risk losing data and this action is certainly not recommended.

Just for the record, we decided to let the recovery finish. It took something like 20 hours. I am pretty sure that the disk subsystem was not a great performer ...

How to identify the version of Sharepoint you are using

At first glance, this seems to be a very simple question, but after a long research in the internet I could not find a clear answer!!!

Thankfully, one of my colleagues had the answer and shared it with me :)

Strange as it may seem, there are actually at least two methods you can do this:

1st Method 

1. Navigate to the SharePoint Central Administration.

2. Click on Site Actions->Site Settings.

3. The version is just below Site Information.

4. Consult the table below to see what the version number means relating to the Service Packs that have been released:

Windows Sharepoint Services V2
RTM: 11.0.5530.0
SP1: 11.0.6361.0
SP2: 11.0.6568.0
SP3: 11.0.8165.0

Windows Sharepoint Services V3
RTM: 12.0.4518.1016 
SP1: 12.0.6219.1000 
SP2: 12.0.6341.5000 

Sharepoint Server Portal 2003
RTM: 11.0.5704.0
SP1: 11.0.6715.0
SP2: 11.0.8126.0
SP3: 11.0.8168.0

Microsoft Office Sharepoint Server 2007
RTM: 12.0.4518.1016 
SP1: 12.0.6219.1000 
SP2: 12.0.6341.5002  

2nd Method

Download and execute the utility called SPSReport on your Sharepoint machine from the Codeplex site:

http://www.codeplex.com/spsreport/Release/ProjectReleases.aspx?ReleaseId=5706

Then examine the build (version) of the DLL files listed in SERVERNAME_MOSSVer.txt, SERVER_WSEVer.txt, and SERVERNAME_GacVer.txt files. The highest build mentioned is the current version. Then consult the table I mentioned aboce to find out what your version means relating to Service Packs.

How to allow a user to view a file over a network share, but at the same time stop him from copying this file?

Recently we had a strange support request that had never occurred to me before:

“Is there any combination of NTFS and share permissions that would allow a user to view a video file over a network share, but at the same time stop this user from copying this video?”

Research and experimentation and research showed that there is no built-in Windows functionality to get the desired result.

When a client wants to copy a file from a server share, there are access checks made at two different levels:

1.    Share permission

2.    NTFS permission (provided that the content is located on an NTFS formatted partition). So anyone with Read Share permission, and Read NTFS permission will be able to copy the file to his/her own machine.

In summary, it looks like the desired result could only be achieved by using content management software.

For example:

1.    Windows Rights Management Software or similar solutions might be able to prevent this:

a.      What this kind of software does differently is that the content (the videos) is kept in encrypted format on the file shares.

b.    So even if a user is able to copy the file via Read permissions, he wouldn't be able to open the content without using the RMS enabled client software and without adding him in the allowed viewers list.

Windows Rights Management Services

http://www.microsoft.com/windowsserver2003/technologies/rightsmgmt/default.mspx

2.    There are also some 3rd party content management software tools that can help, like GigaTrust, who is a Gold Certified Partner of Microsoft:

GigaTrust Dynamic File Folders

http://www.gigatrust.com/dynamic-file-folders.shtml

To summarize:

-       There is no builtin Windows functionality to prevent users from copying a file, on which they have Read access.

-       You will need to setup a Windows RMS infrastructure or use similar software.

How to export events from SQL Server 2005 and send them to the Windows Event Viewer Log

Recently I was asked how to export events from a SQL Server 2005 and send them to the Event Viewer Log, so that the SCOM administrator could see these events.

As I found out, there existed no “out of the box” solution for SQL Server 2005. The workaround we found was to develop our own solution:

·         At first you need to capture a profiler trace:

o    The profiler trace should be saved inside a database table and not inside a file.

o    You should use appropriate filters to avoid capturing unnecessary events

 e.g. filter just for events with TEXTDATA like '%update%', if you want to capture only UPDATE queries

You don’t necessarily need the Profiler Tool to capture a trace. You can configure, start and stop a profiler trace programmatically by using stored procedures like:

sp_trace_setstatus

http://msdn.microsoft.com/en-us/library/ms176034(SQL.90).aspx

sp_trace_setevent

http://msdn.microsoft.com/en-us/library/ms186265(SQL.90).aspx

sp_trace_setfilter

http://msdn.microsoft.com/en-us/library/ms174404(SQL.90).aspx

You could even go as far as creating your own unique events:

sp_trace_generateevent

http://msdn.microsoft.com/en-us/library/ms177548(SQL.90).aspx

·         Then you need to create a trigger on this table (or maybe create a SQL Server scheduled job that is executed periodically).

o    For any new event that is logged inside this table, you can then use the XP_LOGEVENT stored procedure to log a user-defined message in the Windows Event Viewer.

for Reference:

xp_logevent (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms186244(SQL.90).aspx

NOTE

In SQL Server 2008 there is a new feature called Extended Events, which you can use to send specific SQL Events to the Windows Event Viewer:

Using SQL Server Extended Events

http://msdn.microsoft.com/en-us/library/bb630317.aspx

SQL Server Extended Events How-to Topics

http://msdn.microsoft.com/en-us/library/bb630340.aspx