Could not connect to the report server.

Today when I was trying to deploy a Report project using SQL Server 2008 BIDS (Business Intelligence Development Studio), I receieved the following error: Microsoft Report Designer Could not connect to the report server http://computername:8080/ReportServer. Verify that the TargetServerURL is valid and that you have the correct permissions to connect to the report server. Additional information: The operation has timed out (System.Web.Services) And the deployment of the project failed.  I examined the rsreportserver.config located on the \Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer directory and found SSL was configure on the report server. I edited the rsreportserver.config file, and changed the key <Add Key="SecureConnectionLevel" Value="2"/> to <Add Key="SecureConnectionLevel" Value="0"/>. Then I was able to deploy the report project succesfully. ------ Build started: Project: MorilloBasicTableReport, Configuration: Debug ------ Build complete -- 0 errors, 0 warnings ------ Deploy started: Project: MorilloBasicTableReport, Configuration: Debug ------ Deploying to http://computername:8080/ReportServer Deploying report '/MorilloBasicTableReport/Sales by Area'. Deploying report '/MorilloBasicTableReport/Sales Orders'. Deploy complete -- 0 errors, 0 warnings ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Ref: http://www.sqlcoffee.com/Troubleshooting096.htm

Removing unwanted spaces within a string

Removing leading and trailling spaces is pretty easy. All you need to do is make use of Ltrim and Rtrim function respectively. But there are times when you want to remove unwanted spaces within a string. Check out the below code to know how to do it. 


/*Declaration and Initialization */
Declare @strValue varchar(50)
Set @strValue = ' I     Love     you ! ' 


/*Remove the leading and trailing spaces*/
Set @strValue = Rtrim(Ltrim(@strValue))


/*Loop through and remove more than one spaces to single space. */
While CharIndex('  ',@strValue)>0
Select @strValue = Replace(@strValue, '  ', ' ')


/*Final output*/
Select @strValue

Enable SQL Server Authentication and Windows Authentication for SQL Server Databases

Follow below procedure to enable both SQL Server Authentication and Windows Authentication in SQL Server 2008 database, and then add a User login and password account for connecting to the database.

• Open SQL Server Management Studio.
• In the Object Explorer sidebar, right-click on the top SQL Server node, then click Properties.
• In the Server Properties pop-up box, click on Security, then in the Server authentication section select "SQL Server and Windows Authentication mode" and click OK.
• Now in the Object Explorer, open the Security folder node, then right-click on the Logins folder, and select New Login.
• In the New - Login popup, enter in the Login name you want to create, then select SQL Server authentication.  Type in Password and Confirm Password  (Note: the password should have letters and numbers).  Then uncheck Enforce password expiration and uncheck User must change password at next login.  Then change the Default database drop-down box to the database you want to use.
• Then on the left-hand side click User Mapping.  In the Users mapped to this login: section check the Map checkbox next to the database you want the user mapped to.  Then in the Database role membership section, check db_owner. ("dbo" should be added to the Default Scheme automatically for the user login once saved).  Click OK to save changes. 
• The new User/login should be added to the database.  To check to make sure, go the Object Explorer, locate the database and click on the plus sign to open up the node, then open the Security folder node, and open the Users folder, you should see the User you just created is in the list of Users for the database.
• Now we need to assign the user to the "db_owner" scheme and role membership. Right-click on the User name, then in the Database User popup box you will want to make sure that "db_owner" is checked in both Schemes owned by this user and Database role membership.  The click OK.

Now you can use both SQL Server and Windows Authentication connection string to connect to your database like this:


connectionString="data source=.; Initial Catalog=DemoData; Integrated Security=True;" 
Or use a SQL Server Authentication connection string like this:


connectionString="data source=.;Initial Catalog= DemoData ;User Id=myUsername;Password=myPassword;"

Enable Object Explorer in SQL server 2008 R2

Today after installing Microsoft SQL server 2008 R2 on one of developers system, Object Explorer was not getting opened.


To resolve this issue following steps are used and Object explorer got visible


Window —> Reset Window Layout in Management Studio.

How to Add a Linked Server

Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name:

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)


Add New Linked Server
2. The “New Linked Server” Dialog appears.  (see below).


Linked Server Settings
3. For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
4. Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
5. Provider – Select “Microsoft OLE DB Provider for SQL Server”
6. Product Name – type: SQLSERVER (with no spaces)
7. Datasource – type the actual server name, and instance name using this convention: SERVERNAME\INSTANCENAME
8. ProviderString – Blank
9. Catalog – Optional (If entered use the default database you will be using)
10. Prior to exiting, continue to the next section (defining security)

Define the Linked Server Security

Linked server security can be defined a few different ways. The different security methods are outlined below.  The first three options are the most common:

Option Name	Description
Be made using the login’s current security context	Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context	Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings	You can specify multiple SQL Server logins to use based upon the context of the user that is making the call.  So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server.  This will allow you to not need to define “George” on the linked server.
Not be made	If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context	Connect to the server without any credentials.  I do not see a use for this unless you have security defined as public.

1. Within the same Dialog on the left menu under “Select a Page”, select Security
2. Enter the security option of your choice.


Linked Server Security Settings
3. Click OK, and the new linked server is created

Ref: http://sqlserverplanet.com/dba/how-to-add-a-linked-server