SQL Share

Opening SQL Server 2000 DTS Package in SQL Server 2008

2011-11-04T07:50:00.000-07:00

Error:

TITLE: Object Explorer
------------------------------

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

------------------------------
BUTTONS:

OK
------------------------------

Fix: Install SQLServer2005_BC & SQLServer2005_DTS softwares from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988

If you are still not able to get it working. Please follow the below steps:

The issue was with PATH environment variable setting.

After changing the order in the path setting it got resolved.

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\; should appear before the

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;

Incase of 64 bit machine:

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;

Be sure to restart the SSMS after changing the PATH env variable

TSQL Currently logged windows user name

2010-10-11T06:14:00.000-07:00

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

2010-10-10T15:52:00.000-07:00

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

2010-09-30T12:48:00.000-07:00

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

'xp_cmdshell' does not exist

2010-09-23T08:12:00.000-07:00

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

2010-09-23T08:11:00.000-07:00

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

SSIS: Performance Tuning

2010-09-22T13:43:00.001-07:00

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.

2010-09-21T11:41:00.000-07:00

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

2010-09-20T08:15:00.004-07:00

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

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

2010-09-20T08:15:00.003-07:00

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

2010-09-20T08:15:00.001-07:00

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

2010-09-20T08:14:00.005-07:00

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

2010-09-20T08:14:00.003-07:00

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

2010-09-20T08:14:00.001-07:00

USE DatabaseName
GO

DBCC SHRINKFILE(, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(, 1)

GO

SQL : SQL Timeout Firewall Issue

2010-09-20T08:13:00.003-07:00

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

2010-09-20T08:13:00.001-07:00

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.

2010-09-20T08:12:00.005-07:00

“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

2010-09-20T08:12:00.003-07:00

HTML : Striketrhough

2010-09-20T08:12:00.001-07:00

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

2010-09-20T08:08:00.000-07:00

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

2010-09-20T08:07:00.002-07:00

Internet Options > Security Tab

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

SQL : Trasition State, Please Try Later

2010-09-20T08:07:00.001-07:00

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

2010-09-20T08:06:00.003-07:00

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

2010-09-20T08:06:00.001-07:00

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

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

2010-09-20T08:05:00.003-07:00

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

2010-09-20T08:05:00.001-07:00

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

2010-09-20T08:04:00.000-07:00

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

2010-09-20T08:03:00.000-07:00

Right click - > Script -> Create To

Infopath : File upload size limit

2010-09-20T08:02:00.002-07:00

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

2010-09-20T08:02:00.001-07:00

EXEC sp_databases

Infopath: Steps Modify the Infopath form

2010-09-20T08:01:00.000-07:00

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

2010-09-20T08:00:00.002-07:00

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

2010-09-20T08:00:00.001-07:00

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

2010-09-09T08:12:00.000-07:00

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

2010-09-08T07:53:00.000-07:00

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

2010-09-01T06:36:00.001-07:00

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

2010-09-01T06:35:00.000-07:00

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

Thigs to be considered before big import.

2010-09-01T06:31:00.000-07:00

** 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

2010-08-31T08:21:00.000-07:00

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

2010-08-31T08:20:00.000-07:00

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

SQL Server 2005 Linked Server using TSQL

2009-09-10T18:00:00.000-07:00

Use the below to commands to add a server as linked server:

To Add the Server:
sp_addlinkedserver 'Alias Name', '', 'SQLNCLI', NULL, NULL, 'SERVER=IP', NULL

To Add the security:
sp_addlinkedsrvlogin 'Alias Name', 'false', NULL, 'User Name', 'Password'

NOTE: When you are trying to connect SQL Server 2000 to SQL Server 2005 as Linked server you may get the following error:

OLE DB provider "SQLNCLI" for linked server "serv01" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "serv01" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "serv01". The provider supports the interface, but returns a failure code when it is used.

Solution is specified in this link.

What worked for me is this

SELECT * FROM OPENQUERY(LinkedServerAlias,'SELECT * FROM [Fully Qualified Name')

CRM 4.0 Calling Web Service

2009-06-21T09:06:00.000-07:00

public static CrmService GetCrmService(string orgName, string server)
{

CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = orgName;

CrmService service = new CrmService();

service.Credentials = System.Net.CredentialCache.DefaultCredentials;
service.CrmAuthenticationTokenValue = token;
service.Url = string.Format("http://{0}/mscrmservices/2007/crmservice.asmx", server);
return service;

}

How to ouput the results to File (SQL Server)

2009-04-24T08:26:00.001-07:00

Either do Control + shift + F
or
Go to Query -> Result To -> Select "Output to file"
Now when you will run the query, system will ask for the file to save the results.

difference between the console.read and console.readlline

2009-04-17T06:34:00.001-07:00

Console.Readline reads the entire line of data.
Console.Read reads only one character at a time.
Console.Write will writes to the screen and leaves the cursor until the user hits enter.
Console.WriteLine writes to the screen and will perform a carriage return and line-feed.

How to create the proxy class from WSDL

2009-04-15T06:32:00.001-07:00

Open Start-> visual Studio 2008 -> Tools -> Command prompt

browse to the desired directory

and type

wsdl www.wsdlurl.com/wsdl

and a file will be created

How to use Foreach in c# Arrays

2009-04-14T13:42:00.001-07:00

string[] WeeKdays = new string[7];

WeeKdays[0] = Sunday";
WeeKdays[1] = "Monday";
WeeKdays[2] = "Tuesday";
WeeKdays[3] = "Wednesday";
WeeKdays[4] = "Thursday";
WeeKdays[5] = "Friday";
WeeKdays[6] = "Saturday";

foreach (object row in Weekdays)
{
Response.Write(row + " , ");
}

Loop through each row in dataset

2009-04-09T07:56:00.001-07:00

foreach (DataRow row in dsDataset.Tables[0].Rows)
{
Console.writeline(row["ColmnName1"].tostring();
Console.writeline(row["ColmnName2"].tostring();
.
.
.
}

Date Compare in C#

2009-04-09T07:55:00.000-07:00

Visual Basic

Dim t1 As DateTime
Dim t2 As DateTime
Dim returnValue As Integer

returnValue = DateTime.Compare(t1, t2)

C#
public static int Compare(
DateTime t1,
DateTime t2
)

Parameters
t1
Type: System..::.DateTime
The first DateTime.

t2
Type: System..::.DateTime
The second DateTime.

Return Value
Type: System..::.Int32
A signed number indicating the relative values of t1 and t2.

Value Type Condition

Less than zero t1 is earlier than t2.
Zero t1 is the same as t2.
Greater than zero t1 is later than t2.

Example:
C#

DateTime t1 = new DateTime(100);
DateTime t2 = new DateTime(20);

if (DateTime.Compare(t1, t2) > 0) Console.WriteLine("t1 > t2");
if (DateTime.Compare(t1, t2) == 0) Console.WriteLine("t1 == t2");
if (DateTime.Compare(t1, t2) < 0) Console.WriteLine("t1 < t2");

Alternate/simple way to pivot data

2009-04-03T08:43:00.001-07:00

SET NOCOUNT ON
– Prepare sample data
DECLARE @table TABLE (Dept_ID INT, EMP_Name VARCHAR(30))

INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Jack’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘John’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Beth’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Mary’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Allen’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Diana’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Don’)

SELECT
DISTINCT t.Dept_ID,
STUFF((SELECT ‘, ‘ + t2.EMP_NAME FROM @table AS t2 WHERE t2.Dept_ID = t.Dept_ID FOR XML PATH(”)), 1, 2, ”) AS EMP_NAMES
FROM @table AS t
ORDER BY t.Dept_ID

Output is shown below:

Dept_ID EMP_NAMES
———– ————————
10 Jack, John, Beth
20 Mary, Allen, Diana, Don

SQL PIVOT

2009-04-03T08:38:00.000-07:00

The PIVOT statement is used for changing rows into columns in a SQL Query (Crosstab). The PIVOT Statement is generally written in this form:

SELECT columns
FROM table
PIVOT
(
Aggregate Function(Measure Column)
FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias
Note: You must use brackets around each of the Pivot Column Values

Example

declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)

select * from @sales
pivot
(
sum(Amount)
for Quarter
in (Q1, Q2, Q3, Q4)
) as p

Year Q1 Q2 Q3 Q4
----------- ---------------------- ---------------------- ---------------------- ----------------------
2001 220 15 50 170
2002 20 25 20 35

(2 row(s) affected)

How to move the system databases

2009-04-02T14:03:00.000-07:00

Step 1) Open Notepad,copy the below script there and save the file as "MoveSql2005SysDbs.cmd".

Script:

@echo off
REM /////////////////////////////////////////////////////////////////////
REM // MoveSql2005SysDbs.cmd - Execute commands to move system databases
REM /////////////////////////////////////////////////////////////////////
if "%2"=="" goto syntax

set InstName=%1
set NewPath=%2
set ScriptPath=%~dp0

if /I %InstName% equ MSSQLSERVER (
set ServiceName=MSSQLSERVER
set AgentServiceName=SQLSERVERAGENT
set SQLName=.
) else (
set ServiceName=MSSQL$%InstName%
set AgentServiceName=SQLAgent$%InstName%
set SQLName=.\%InstName%
)

REM /////////////////////////////////////////////////////////////////////
REM // Ensure we can find our SQL script file
REM /////////////////////////////////////////////////////////////////////
if not exist %ScriptPath%MoveSql2005SysDbs.sql (
echo This command MUST be run from the SQL Server and the script file MoveSql2005SysDbs.sql
echo must be in the same directory as the MoveSql2005SysDbs.cmd file.
exit 1
)

REM /////////////////////////////////////////////////////////////////////
REM // Check viability of parameters before any changes happen...
REM /////////////////////////////////////////////////////////////////////
sqlcmd -E -S%SQLName% -Q"print 'Instance name $(InstName) verified.'"
if errorlevel 1 (
echo Sql Server %SQLName% not found, please check instance name parameter.
exit 1
)

dir %NewPath% > nul
if errorlevel 1 (
echo New Path %NewPath% not found, please check path parameter.
exit 1
)

REM /////////////////////////////////////////////////////////////////////
REM // Now start modifying the system database locations
REM /////////////////////////////////////////////////////////////////////
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = '%NewPath%\model.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = '%NewPath%\modellog.ldf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = '%NewPath%\MSDBData.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = '%NewPath%\MSDBLog.ldf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = '%NewPath%\tempdb.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = '%NewPath%\templog.ldf')"

REM /////////////////////////////////////////////////////////////////////
REM // This script file finds the correct spot in the registry for the startup parameters, replaces the path, and returns
REM // the old path name back to the calling script.
REM /////////////////////////////////////////////////////////////////////
for /f "delims=;" %%s in ('sqlcmd -E -S%SQLName% -h-1 -i%ScriptPath%MoveSql2005SysDbs.sql') do set OldPath="%%s"

echo Shutting down SQL Server. Answer Y if prompted to shut down dependent services.
net stop %AgentServiceName%
net stop %ServiceName%

echo Moving files...
move %OldPath%\model.mdf %NewPath%
move %OldPath%\modellog.ldf %NewPath%
move %OldPath%\MSDBData.mdf %NewPath%
move %OldPath%\MSDBLog.ldf %NewPath%
move %OldPath%\tempdb.mdf %NewPath%
move %OldPath%\templog.ldf %NewPath%
move %OldPath%\master.mdf %NewPath%
move %OldPath%\mastlog.ldf %NewPath%

echo Restarting service with /f and trace flag 3608
net start %ServiceName% /f /T3608

sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'data', FILENAME = '%NewPath%\mssqlsystemresource.mdf')"
sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = '%NewPath%\mssqlsystemresource.ldf')"

move %OldPath%\mssqlsystemresource.mdf %NewPath%
move %OldPath%\mssqlsystemresource.ldf %NewPath%

sqlcmd -E -S%SQLName% -Q"ALTER DATABASE mssqlsystemresource SET READ_ONLY"

net stop %ServiceName%

echo Restarting service %ServiceName% in normal mode
net start %ServiceName%
net start %AgentServiceName%

echo Verifying new location of system databases...
sqlcmd -E -S%SQLName% -Q"SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files ORDER BY database_id;"
exit 0

:syntax
echo MoveSql2005SysDbs - Moves sql system databases to a different directory
echo MoveSql2005SysDbs [InstanceName] [NewPath]
echo InstanceName should be MSSQLSERVER if using the default instance
echo NewPath should contain a full path but no trailing backslash

:end

Step 2) Open notepad, copy the sql script below and save it as "MoveSql2005SysDbs.sql".
Script:

SET NOCOUNT ON
DECLARE @MssqlDotNum varchar(100), @key varchar(255), @ArgName varchar(255), @Arg varchar(255), @i int, @OldPath varchar(255)

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', '$(InstName)', @MssqlDotNum output

SELECT @i = 0

SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',
@ArgName = 'SQLArg' + convert(char(1), @i)

WHILE @i <= 2
BEGIN
SELECT @key = 'Software\Microsoft\Microsoft SQL Server\' + @MssqlDotNum + '\MSSQLSERVER\Parameters',
@ArgName = 'SQLArg' + convert(char(1), @i)

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @key, @ArgName, @Arg output

IF left(@Arg,2) = '-d'
select @OldPath = substring(@Arg, 3, charindex('\master.mdf', @Arg) - 3)
ELSE IF left(@Arg,2) = '-l'
select @OldPath = substring(@Arg, 3, charindex('\mastlog.ldf', @Arg) - 3)

IF left(@Arg,2) IN ('-d','-l')
BEGIN
select @Arg = replace(@Arg, @OldPath, '$(NewPath)')
exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', @key, @ArgName, 'REG_SZ', @Arg
END
SELECT @i = @i + 1
END
-- Return the path to the calling .CMD file so it knows the old path. ; is the delimiter
select OldPath = rtrim(@OldPath) + ';'

Step 3) Open notepad, copy the script below. Change the location where you want to move the databases to and save the file as "MoveSql2005SysDbs__run.bat".

Script:

MoveSql2005SysDbs.cmd MSSQLSERVER d:\MSSQL\SystemDB

Step 4) Save all 3 files into same folders.

Step 5) Double click the bat file and once it finish the processing please verify that files are moved to specified location.

In case files are not moved the you need to run the bat file from command prompt.

How to Query 2 the tables on different server

2009-04-02T07:55:00.001-07:00

First of all the Server must be added as Linked server using the below command

sp_addLinkedserver 'servername' w

then the table name should be in a full four part format

ServerName.DatabaseName.DatabaseOwner.TableName

for example TestSvr.TestDB.DBO.Testtbl

Exclusive access could not be obtained because the database is in use

2009-04-01T10:38:00.000-07:00

Issue: Exclusive access could not be obtained because the database is in use
Solution:

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Installing C360 Field Level Security on 64 bit server

2009-01-23T06:48:00.000-08:00

After installing C360 FLS on 64 bit, the CRM system freeze and does not allow to click anywhere or open anything without giving any error.

To fix that issue Please follow the steps below:

1) Launch IIS Manager (INetMgr from Start/Run).

2) Right-click on the Microsoft Dynamics CRM website and click 'Properties'.

3) Select the 'Home Directory' tab.

4) Click 'Configuration'.

5) Under 'Application Extensions', highlight '.htc' and click 'Edit'.

6) Click 'Browse...' and select

7) 'C:\Windows\Micosoft.Net\Framework64\v2.0.50727\aspnet_isapi.dll'.

8) Uncheck ‘Verify the that the file exists’.

9) Click 'OK'.

10) Do not select any child nodes if prompted for Installation Overrides.

11) Click 'OK'.

12) Execute an IISReset.

In windows server 2008 the steps will be like this:

1) Launch IIS Manager (INetMgr from Start/Run).

2) Click on Microsoft Dynamics CRM website.

3) Double click on Handler Mappings.

4) highlight '.htc' and click 'Edit'.

5) Click 'Browse...' and select

6) 'C:\Windows\Micosoft.Net\Framework64\v2.0.50727\aspnet_isapi.dll'.

7) Click 'OK'.

8) Execute an IISReset.

Reference : Click here

Asp.Net (C#)

2008-10-09T10:14:00.001-07:00

Null and ToString (Asp.Net)

Quotes

2008-10-09T10:13:00.007-07:00

MS SQL Server (Administration)

2008-10-09T10:13:00.005-07:00

Shrink DB Size

SSRS

2008-10-09T10:13:00.003-07:00

SQL Server Reports (Coming Soon)

SSIS

2008-10-09T10:13:00.001-07:00

Error while importing from Flat File

SSIS: Create SMTP (Mail Server) Connection

SSIS: Creating Data Source

SSIS: Create File Connection

MS SQL Server (T-SQL)

2008-10-09T08:42:00.000-07:00

SQL NOT IN Constraint and NULL values
Line Break In Dynamic SQL

Learn Spnish

2008-10-09T08:39:00.001-07:00

Its Friday Finally -- su el viernes por ultimo.

Null and ToString (Asp.Net)

2008-10-08T19:34:00.000-07:00

Sometimes while using ToString method for a object we end up in a situation where object is null and we get Null Exception. eg

Object 0 = null;
o.ToString();

In this case we can use if condition to check whether the object is null and then use ToString method.

if (o != null)
{
o.ToString();
}

SQL NOT IN Constraint and NULL values

2008-10-06T11:51:00.000-07:00

Few days back i was working on a query which involved "IN". To verify the result i am getting i used "NOT IN" and guess what... i get 0 result.After spending couple of hours on the issue i found that if in the subquery there is any NULL value then query will return a count of o records.
According to SQL Online Book:
"Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results."
Click http://msdn.microsoft.com/en-us/library/ms177682(SQL.90).aspx to read more
On another website i got better example to explain:
To state it simply, why does query A return a result but B doesn't?
A: select 'true' where 3 in (1, 2, 3, null)B: select 'true' where 3 not in (1, 2, null)
This was on SQL Server 2005. I also found that calling set ansi_nulls off causes B to return a result.
The most accepted reason for this behaviour is:
Query A is the same as:select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = nullSince 3 = 3 is true, you get a result.
Query B is the same as:select 'true' where 3 <> 1 and 3 <> 2 and 3 <> nullWhen ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.
When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row
And the another reason i found which explains it well is as follows:
In A, 3 is tested for equality against each member of the set, yielding (FALSE, FALSE, TRUE, UNKNOWN). Since one of the elements is TRUE, the condition is TRUE. (It's also possible that some short-circuiting takes place here, so it actually stops as soon as it hits the first TRUE and never evaluates 3=NULL.)
In B, I think it is evaluating the condition as NOT (3 in (1,2,null)). Testing 3 for equality against the set yields (FALSE, FALSE, UNKNOWN), which is aggregated to UNKNOWN. NOT ( UNKNOWN ) yields UNKNOWN. So overall the truth of the condition is unknown, which at the end is essentially treated as FALSE

Line Break In Dynamic SQL

2008-10-03T05:51:00.000-07:00

While writting dynamic sql you might get into situation where you want to separate the code in next line. For that purpose use

Char(13) + char(10) and this is will give a line break in the code you are writing in dynamic sql.

Error while importing from Flat File

2008-10-02T06:38:00.000-07:00

Sometime while importing the data from Text file to SQL Server 2005 table we keep getting the below error and the same data can be imported to SQL Server 2000 without any issue.
"Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".(SQL Server Import and Export Wizard)"
Initially i was also convinced that this is definitly a bug in SQL Server 2005. But i was wrong. The reason why SQL gives this error is because Flat File defaults initially all the columns to Characters with length of 50.And if your data length in column is more than 50 you get this error.
The easy fix for this issue is.
While you select the text file as data source go to "Advanced" tab and Set the size of the column length to be according to what you want.
And you are all set.

Shrink DB Size

2008-09-26T18:24:00.000-07:00

Many times we delete/truncate the data from database but database does not release the space to system after that. In that case DBCC ShrinkDatabase and DBCC ShrinkFile can be usefull.

This is something important information about DBCC ShrinkDatabase.

"The NOTRUNCATE option, with or without specifying target_percent, performs the actual data movement operations of DBCC SHRINKDATABASE including the movement of allocated pages from the end of a file to unallocated pages in the front of the file. However, the free space at the end of the file is not returned to the operating system and the physical size of the file does not change. Therefore, data files appear not to shrink when the NOTRUNCATE option is specified.The TRUNCATEONLY option reclaims all free space at the end of the file to the operating system. However, TRUNCATEONLY does not perform any page movement inside the file or files. The specified file is shrunk only to the last allocated extent. target_percent is ignored if specified with the TRUNCATEONLY option."

Personally i would prefer DBCC ShrinkFile and Shrink each file (mdf/ldf) separatly.

If you have very big database then do it off peak times.

SSIS: Create File Connection

2008-08-11T17:45:00.000-07:00

· Right click anywhere in the Connection Managers pane.
· In the Types list select File and click ADD.
· In Usage type section select the appropriate option from the dropdown.
** If Create File is selected then new file will be created to the specified location with the given name.
· Click browse and select the location for the file.
· Click OK.

SSIS: Create SMTP (Mail Server) Connection

2008-08-11T17:37:00.000-07:00

· Right click anywhere in the Connection Managers pane.
· In the Type select SMTP and then click ADD.
· Provide suitable name to the connection.
· Enter the name of the SMTP server.
· Select suitable option for Windows Authentication and Enable SSL.
· Click OK.

SSIS: Creating Data Source

2008-08-11T17:20:00.000-07:00

· Right click data sources and select New Data Source.
· Click on New.
· In provider dropdown select Microsoft OLEDB Provider for SQL Server or suitable connection provider according to your data source.
· Enter the server credentials.
· Click on Test Connection to verify the connection.
· Click OK.
· Hit Next.
· In Data Source Name give proper name to your data source connection and verify the connection string in preview box.
· Click Finish.

Quotes

2008-07-29T09:14:00.001-07:00

When i read about the evils of drinking, i gave up reading.

To achieve the impossible dream, try going to sleep.

SQL Server Reports (Coming Soon)

2008-07-28T13:39:00.001-07:00

SQL Server Reports (Coming Soon)