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')
Thursday, September 10, 2009
Sunday, June 21, 2009
CRM 4.0 Calling Web Service
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;
}
{
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;
}
Friday, April 24, 2009
How to ouput the results to File (SQL Server)
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.
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.
Friday, April 17, 2009
difference between the console.read and console.readlline
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.
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.
Wednesday, April 15, 2009
How to create the proxy class from WSDL
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
browse to the desired directory
and type
wsdl www.wsdlurl.com/wsdl
and a file will be created
Tuesday, April 14, 2009
How to use Foreach in c# Arrays
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 + " , ");
}
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 + " , ");
}
Thursday, April 9, 2009
Loop through each row in dataset
foreach (DataRow row in dsDataset.Tables[0].Rows)
{
Console.writeline(row["ColmnName1"].tostring();
Console.writeline(row["ColmnName2"].tostring();
.
.
.
}
{
Console.writeline(row["ColmnName1"].tostring();
Console.writeline(row["ColmnName2"].tostring();
.
.
.
}
Date Compare in C#
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");
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");
Friday, April 3, 2009
Alternate/simple way to pivot data
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
– 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
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)
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)
Thursday, April 2, 2009
How to move the system databases
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.
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
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
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
Wednesday, April 1, 2009
Exclusive access could not be obtained because the database is in use
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'
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'
Friday, January 23, 2009
Installing C360 Field Level Security on 64 bit server
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
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
Subscribe to:
Posts (Atom)