SQL Server Alias to connect to database

I have got a very good article which explains how to create SQL Server Alias to connect from client to server for named instance of SQL database

http://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instances/
-----------------------------------------------------------------------------
When connecting to SQL Server from your PC or from your application/web servers, you can choose to create aliases for connectivity. Before we start getting into the nitty-gritty details of aliases, let us see how you can set one up. You can use SQL Server Connection Manager in SQL Server 2005 to set it up and in case the tools are not installed, then you can also use cliconfg.exe (note – there is no i in confg) which is located under C:\Windows\System32 folder. If you use the connection manager, you will get a screen like this one:

You can then create a new alias as shown in the next image:

If you are using cliconfg.exe (the same is called in SQL Server 2000 when you use the SQL Server Client Network Utility), you will get a dialog box like the one shown below (showing the alias tab):

As you can see from above, the same alias that we had created using the SQL Server Connection Manager is automatically visible using the cliconfg.exe as well – that is because both of them read this entry from the registry. The place where these connection aliases can be found in the registry is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

So, now that we have seen how to go about creating an alias and where it is stored, what exactly is the use of this feature? There are a couple of benefits of using aliases:

1) An alias can be used to define a user-defined name for connecting to a server -even if the IP address changes later on, there are no changes to the connection string – you just need to update the alias and the application will keep on working as before. You will not need to specify any instance name in the application connection string(s). And using an IP address directly in the alias definition can also save you some time in doing the DNS look-up.

2) You can make connection to SQL Server using different protocols: TCP/IP, Named Pipes etc. and specify specific parameters like the TCP port or the pipe name etc.

3) Aliases are also good for performance reasons. Since an alias has a pre-defined protocol, it can help you speed up the connection. Think about what happens when a connection is made to SQL Server from a client application. It has to try various protocols in the order that they are defined in your SQL Server Connection Manager (SQL 2005) or the SQL Server Client Network Utility (SQL 2000). If you already know which protocol and port your server is listening on, by configuring an alias you can by-pass the discovery phase that SQL Browser service goes through. In addition, if you know and have benchmarked your application using a specific protocol, you can standardize the alias technique across the board.

many thanks to decipherinfosys
---------------------------------------------------------------------------
After doing the above

From SQL Server Configuration Manager - SQL Native Client Configuration - Aliases , i can see my alias. when connecting using alias from SQL Server Management Studio didn't work . in my Configuration Manager there is another SQL Native Client Configuration (32bit). i had to create alias there in order for my client to connect.

Other references:-

http://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx
http://support.microsoft.com/kb/265808

File Receive Location is disabled in BizTalk

File receive location is disabled in BizTalk 2009 even if you have configured file receive adapter and receive location in BizTalk Administration.

You receive a "The receive location '[drive:]\[path]\*.xml' is shutting down" error message

share the directory and use unc path for receive location \\servername\sharename

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

give permission only to biztalk host instance users on the share

Access Denied-Deploy BizTalk project

You get access denied message when deploying BizTalk 2009 project from Visual Studio 2008 on MS Windows 2008 R2 OS.

run Visual Studio 2008 as Administrator when deploying

Oracle Database connection problem after configuring client

Connecting to the LOB system has failed.
Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral,
PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file
specified..

i was getting this error when using Consume Adapter Service from Visual Studio 2008 to connect to oracle database using OracleDBBinding configuration.

i could not see Oracle.DataAccess in the c:\windows\assembly folder. i was using instantclient to connect to oracle server

downloaded Oracle 11g ODAC 11.1.0.7.20 with Oracle Developer Tools for Visual Studio OTN website

http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html (ODAC 11.1.0.7.20 with Oracle Developer Tools for Visual Studio). install it clicking on setup.exe

now add generated items from visual studio project .select consumer adapter service.
click on Add . Select a binding --> OracleDBBinding
configure a URI --> oracledb://yourdbalias

click on configure in URI properties tab make sure data source name is correct.

in Binding properties tab changed EnableBizTalkCompatibilityMode --> true
and UseAmbientTransaction --> false

in security tab select Client Credential type to username. give the username and password
otherwise it gives username and password error.
click ok and connect you get error .

Connecting to the LOB system has failed.
ORA-1017: invalid username/password; logon denied.

enter username in CAPITAL letters.

it works

BizTalk 2009 Adpter Pack - No valid Apapters error

After creating a new BizTalk project . right click on the newly created project Add generated items.in generate schema files --> Consume Adpater Service . to connect to the oracle database and retrieve packages or tables whatever you want to use in biztalk for schema files.

it gives me error

"No valid adapters are installed on this machine"

but adapter pack 2.0 is installed and i rechecked ,repaired etc . no luck

solution:

install both 32 bit and 64 bit biztalk adapter pack on 64 bit servers . it will work as some of the 32bit dlls are required for Visual studio . my visual studio was 32 bit :)

Cannot Create BizTalk Project from VS 2008

After a new installation of BizTalk 2009 with all the prerequisites steps from microsoft i was not able to create a new biztalk project from Visual studio 2008.When you try to create a new Biztalk project using

File -> New Project -> Any BizTalk Project Type

You get the error message: Creating project ‘project name’… project creation failed.

most of the users tells me reinstallation of BizTalk that was not a good solution for me as i have been fixing many errors during the BizTalk installation and i don't want to go through the same process again and i need to finish with this implementation as it is dependent on few other projects. oh i am stuck again.

again the registry change . though it is not preferred always if you know what you are doing it then go for it. it could come handy.here is what i did. search for {FAE04EC0-301F-11d3-BF4B-00C04F79EFBC} under projects

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}]

the value for "PossibleProjectExtensions"="csproj" change it to "csproj;btproj" remove the "

for 64 bit versions of Visual Studio the path is

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}

now restart the Visual Studio it works. what i came to know is Biztalk installation modifies the reg value to register its project extension with the C# project factory.thanks to the blogger.

MSDTC logon changed

i have no idea why did i changed the MSDTC logon user . it is so stupid to change it . don't ever try that u will be lost.

its from my event viewer messages that i did that because it gave me error network service does not have permission to access. i don't remember exactly what was the problem. i was doing the configuration for BizTalk 2009 it don't connect to the SQL Server on remote location.

what i did was is from run command open Component Services using command "dcomcnfg". right click My Computer in component services and go to properties --> click on MSDTC tab --> then Security Configuration--> changed the username from network service to my domain user for BizTalk and restarted the server as i thought it will solve the problem. but it did not. not only that i couldn't revert it back. the MSDTC tab is completely gone from properties of My Computer.

now the next option is change in registry for this server. i need to it as my server is in production cluster and a reinstallation of MSDTC is not an option for me as it could affect other production servers on cluster.

from registry --> HKLM --> System --> Current Control Set --> Services --> MSDTC --> Object Name changed to NT Authority\Network Service.

restarted the server. i got the MSDTC tab back but security configuration is disabled. click on Stop --> click on take ownership --> now click on Start . there you get it. change it from here. if you haven't changed it before from registry u should try this step first . few times it gave me message cannot start msdtc service but it is started from some reason.