TSQL Currently logged windows user name

SYSTEM_USER

Example:

DECLARE @sys_usr char(30)

SET @sys_usr = SYSTEM_USER

SELECT 'The current system user is: '+ @sys_usr

GO

Your Current Security Settings Do Not Allow This File To Be Downloaded

Through trial and error, I found that in addition to enabling "File Download" (Tools > Internet Options > Security (tab) > Internet > Custom Level > Downloads >> File Download) you also have to Enable (or set to Prompt) these other two options:
"Launching applications and unsafe files" and
"Launching programs and files in an IFRAME" (this one is only needed if your download occurs in an IFrame),
These 2 options are found under the "Miscellaneous" subsection, on the same settings screen as the "File Download" option.

After making these changes, I was then able to download files from sites, without having to put them into the "Trusted Sites" zone.
Hope this helps/works for others.

MS Excel Spell Check

Select the rows/fields you want to run the spell check for and hir F7.

'xp_cmdshell' does not exist

In order to configure xp_cmdshell, first need to enable the advanced options and then only all the advanced configuration options works out.

It can be done as follows:


EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Refer http://msdn.microsoft.com/en-us/library/ms189631.aspx for more options.

SQL: Enabling xp_cmdshell in SQL Server 2005

http://www.mssqltips.com/tip.asp?tip=1020

SSIS: Performance Tuning

http://www.simple-talk.com/sql/ssis/sql-server-2005-ssis-tuning-the-dataflow-task/

SSIS: [Send Mail Task] Error: Either the file "FileName" does not exist or you do not have permissions to access the file.

This is because when you create the file it put a lock on the file in SSIS.
Solution: Close the file using FileStream.Close() method.

FileStream File1 = new FileStream("C:\Text1.txt", FileMode.Create);
File1.Close();

CRM Reports Fix

1. Enable the SQL Server from Deployment Manager
2. Re-Publish the reports

SharePoint: Deactivated users are still showing up in Staff Directory.

Deactivated users are still showing up in Staff Directory.
1. On the Site Settings page of the portal site, on the User Profile, Audiences, and Personal Sites page, click Manage profile database.
2. On the Manage Profile Database page, click Configure profile import.
3. On the Configure Profile Import page, in the Source area, click Custom source.
4. Click OK.
5. On the Manage Connections page, do one of the following as appropriate to your situation:
o Click the name of the domain that you want to edit, and then click Edit.

-or-
o Click New connection to add a new domain controller that contains the user profiles that you want to import.
.
6. On the Edit Connection or Add Connection page (as appropriate to your situation), in the Search Settings area, do the following:
a. In the Search base box, type the distinguished name (DN) of the Active Directory object from where you want to import the user profiles.

The DN of the search base object defines the location in Active Directory where you want to start your search. The following are examples of DNs:
 DC=DomainName, DC=com
 CN=Users, DC=DomainName, DC=com
 OU=OrganizationalUnit, DC=DomainName, DC=com
b. In the User filter box, type the following LDAP search filter:
(&(objectCategory=person)(objectClass=user)( !(userAccountControl:1.2.840.113556.1.4.803:=2)))
c. Under Scope, specify the scope level, page size, and page time-out options that you want.
1. Click OK.

CRM : An error occurred while promoting a Microsoft CRM e-mail message

Attachment of size bigger than 8 mb are not allowed to be tracked in CRM 4.0

Work Arounds:

You need to change the
following 2 lines in your web.config file to increase the size beyond 8MB

1.

This maxRequestLength determines the size of files being attached in CRM as
well as adjusting the size in the Settings area.

2.

This second maxRequestLength line determines the size of email attachments
that can get tracked when users press track in Outlook.


Note:

Editing web.config did not resolve the issue, but when I changed the 'Maximum
file size' value in Settings>System Settings>E-Mail tab it worked.

Location of web.config: c:\INetpub\www

SQL : Database cannot be opened due to inaccessible files or insufficient memory or disk space

STEP1: identify the db status:
use master
select databaseproperty(‘db_name’,'isShutdown’)
Most of them it would return 1 in this situation
STEP2: Clear up the internal db status:
use master
alter database db_name set offline
it would return with no error in most cases
STEP3: Get detail error message:
use master
alter database db_name set online
After step3, sql server will first verify the log file, if the log file is okay, it will verify the rest of the data file(s). Most of time it is because of the file location or file properties setting. For example if it is file location issue:
alter database db_name
modify (file=’logical name’, filename=’physical name’)
go

SQL : SQL Timeout from Management Studio

I get the message - "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" - when working with large tables in Management Studio.
If you change the Execution time-out you need to open a new query over a new connection:
File -> New -> Database Engine Query
Instead of changing the execution time-out through Tools -> Options you should be able to specify it when opening the first query/connection to a SQL Server in the "Connect to Database Engine" dialog box:
Options -> Execution time-out.

SQL Server : Transaction Log Full

USE DatabaseName
GO

DBCC SHRINKFILE(, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(, 1)

GO

SQL : SQL Timeout Firewall Issue

Everytime after the Server updates, it resets the Firewall option to ON and which causes anyone to connect to SQL.

Go to Control Panel -> Firewall -> Set it to Off.

CRM : Creating Replica of Existing Organization

1. Create new organization
2. Create all new user accounts
3. Create a new “Account” with name “Org User”
4. Import the customizations
5. Import the data for Species
6. Create new Subject lists for abuse types
7. **Confirm everything**
8. Publish the Customizations
9. **Test**
10. Register the plugin.

CRM : Outlook installation on Exchange Server 2003 issue.

“Microsoft Dynamics CRM for Outlook cannot be installed on a computer where Microsoft Exchange Server versions prior to Exchange Server 2007 are installed.”

Fix:

1. Open registry editor.
2. Export everything under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange key.
3. Delete the key.
4. Install CRM client and then re-import the key back in.

Javascript: iFrame Killer

HTML : Striketrhough

To place text onto your site that appears to be crossed out, we use the tag.


Hello

Hello

SQL Server: Changing from Single User Mode to Multiple User Mode

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'databasename'
go


kill 64
go


EXEC sp_dboption 'databasename', 'single user', 'false'

CRM: Popup Issue

Internet Options > Security Tab

Disable (uncheck) “Enable Protected Mode” for Local intranet

SQL : Trasition State, Please Try Later

One of our SQL 2005 database started to give us "Database is in
transition...Error 952". We were trying to Take the DB offline when this
problem occurred. Restarting SQL service did not help us. We were unable to do anything with this database as we were unable to obtain any locks.

What resolved the problem? Re-starting manamgement studio on client machine.
Go SQL!

Sharepoint: Max Character Limit

How to set the maximum field length in InfoPath
by S.Y.M. Wong-A-Ton
Learn how you can use a data validation condition to check the maximum field length in InfoPath and display an error message to the user if the maximum field length is exceeded.
If you’re used to programming in Visual Studio, you’ll be disappointed to know that there is no MaxLength property that you can set on fields in InfoPath that can hold multiple lines of text with line breaks.
So if you want to set the maximum field length on a multi-line InfoPath Text Box (that allows line breaks) or Rich Text Box, you must use data validation.
Note: You can limit the amount of characters on a single-line text box by going to the Properties dialog box of the text box, clicking on the Display tab, and selecting the Limit text box to checkbox.
When using data validation to restrict the maximum field length in InfoPath, you can use the string-length function in a The expression data validation condition to check whether the amount of characters entered in a field is greater than the maximum characters allowed, and then display an error message.
For example, in Figure 1, I’ve created a data validation condition to check whether the maximum length of a piece of text in a multi-line text box (field1) exceeds 10 characters. This technique also works for rich text boxes in InfoPath.
Caveat: Spaces and line breaks are counted as characters.

Sharepoint: Individual File Size

Central Admin --> Application Management --> Web application general settings --> Maximum Upload Size

Sharepoint : "Value does not fall within the expected range"

This definitely happens when some field is missing. Compare your code and the list of fields that all the fields exist and if exist then they have same name. Make sure about the spelling checks.

Sharepoint : Multiline Text Field Size Limit

Error : " This field can have no more than 255 characters"

Go to form Library Settings. Open the properties of the desired field. set "Yes" to "Allow unlimited length in document libraries" settings

Javascript : Finding the Specific Form Field in the Form

for (i = 0; i < document.forms[0].elements.length; i++) {
if (document.forms[0].elements[i].name == 'src_Field1_general') {
document.forms[0].elements["src_Field1_general"].value = '<%=Request.QueryString("src")%>';
}
if (document.forms[0].elements[i].name == 'src_Field2_packs') {
document.forms[0].elements["src_Field2_packs"].value = '<%=Request.QueryString("src")%>';
}
}

SQL Server: Get the Location of Full Text

Right click - > Script -> Create To

Infopath : File upload size limit

Admin tools -> Central Admin -> Application Management -> Under Info-path forms services go to configure info-path forms services à Change the Form Session State to have a maximum size of 25000 KB.

SQL Server : Get the list of Databases with Size

EXEC sp_databases

Infopath: Steps Modify the Infopath form

1. Download the copy of the specific form to the desktop.
2. Delete the form from the Production Form Library.
3. Deactivate the feature from the Site Collection
4. Delete the form from Production central admin -> Manage Infopath form
5. Publish the form back again to Production form library
6. Upload the form to production from Central Admin -> Upload a new form template

Sharepoint: Efficient way of handling User Pemissions

1. All the users should be added to distribution group in AD and then from there we should add the specific AD groups to Sharepoint group rather than one by one adding users in Sharepoint Group
2. Infopath email recipients should be AD distribution

Reduce Log size during Reindexing

USE [Customer]
GO

SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

DBCC SHRINKFILE (Customer_Log, 1)
GO

BACKUP LOG Customer WITH NO_LOG
GO

DBCC SHRINKFILE (Customer_Log, 1)
GO

BACKUP LOG Customer WITH TRUNCATE_ONLY
go

DBCC SHRINKFILE (Customer_Log, 1)
GO

Saving BLOB columns to Folder Directory

If you are migrating the data from a table with a BLOB type data (Excel,Doc,XML,PDF) and you save them separately on the folder, you can use SSIS Export Column transform

Go SSIS!!!

SSIS: Execute SQL Task - IsQueryStoredProcedure

The property "IsQueryStoredProcedure" under "Execute SQL Task" will be available for settings only if you are using ADO.Net connection type. Otherwise it will be greyed out.

MS Dynamic CRM: Useful Error Links

Unauthorized 401 Error

http://nishantrana.wordpress.com/2008/11/06/the-request-failed-with-http-status-401-unauthorized-mandatory-updates-for-microsoft-dynamics-crm-could-not-be-applied-successfully/

CRMDiscoveryService.asmx is not available.

Do Not type the Org name in url.
http://social.microsoft.com/Forums/en-US/crmdeployment/thread/b6ddd9eb-6609-4297-8c35-1537068972e6?prof=required

MS Dynamic CRM: Reports Fix

1. Enable the SQL Server from Deployment Manager
2. Re-Publish the reports

Thigs to be considered before big import.

** Do Import process off times.
• Pass the values directly from source . Don't create any GUID or any column value in the import process.
• Increase the size of the CRM database to big enough to avoid the size increments
• Stop the backup jobs on the server
• Try to put the Data files, Log files and Backup up files on different drives
• Rebuild the indexes and Pad them up to 50% (Because it's a write operation)
• Disable all the organizations
• Put the batch size of 50000 in the SSIS

C# Insert

public static void InsertCustomer(string FName,string LName,string Email,string Location)
{
string sqlConnString = null;

// Get connection string
sqlConnString = ConfigurationSettings.AppSettings["dbConn"].ToString();

using (SqlConnection sqlConn = new SqlConnection(sqlConnString))
{
try
{
//Create command.
SqlCommand command = new SqlCommand("usp_InsCustomer", sqlConn);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 30;

//Build parameter list.
command.Parameters.Add(new SqlParameter("@FName", SqlDbType.NVarChar, 256));
command.Parameters.Add(new SqlParameter("@LName", SqlDbType.NVarChar, 256));
command.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar, 256));
command.Parameters.Add(new SqlParameter("@Location", SqlDbType.NVarChar, 256));
//command.Parameters.Add(new SqlParameter("@IPAddress", SqlDbType.NVarChar, 256));

//Add values for the parameters.
command.Parameters[0].Value = FName;
command.Parameters[1].Value = LName;
command.Parameters[2].Value = Email;
command.Parameters[3].Value = Location;
command.Parameters[4].Value = NumberOfHugs;
//command.Parameters[5].Value = IPAddress;

//Open Connection
sqlConn.Open();

//Fire Command
command.ExecuteNonQuery();

}
catch (Exception ex)
{
//Eat exception
}
}

}

Database Differential Backup Restore

USE master

restore database [DBName]
from disk = 'D:\SQL2005\Backups\Temporary Backups\User DB Backups\FullBackup\DBBackupFolder\DB_backup.bak'
with move 'DBDataFile' to 'D:\SQL2005\Data\DBDataFile.mdf',
move 'DBLogFile' to 'D:\SQL2005\Data\DBLogFile_log.ldf',
norecovery, Replace

--now the diff backup
restore database [PMPoint_0624]
from disk = ' D:\SQL2005\Backups\Temporary Backups\User DB Backups\FullBackup\DBBackupFolder\DB_Restore_backup.bak'
with move ''DBDataFile' to 'D:\SQL2005\Data\DBDataFile.mdf',
move 'DBLogFile' to 'D:\SQL2005\Data\DBLogFile_log.ldf',
recovery