Terminal Server – Pros and Cons – Also Remote Desktop Connection

Terminal Server has some pros-some cons.

Advantages

  • Set one machine up. Works for all
  • Reduced hard-ware costs
  • Remote SQL login

Disadvantages

  • Multi-monitor support *1
  • Seems slow on graphics
  • Remote SQL login does not have intelli-sense

*1 Multi-monitor support for Remote Desktop connection

SplitView http://www.splitview.com/

Also from a cmd prompt use mstsc /span will allow a span. But both monitors need to be the same resolution and horizontal. Does work.

How to set resolution for Remote Desktop Connection when you are using 2 monitors

When switching a RDC from the primary to a smaller second monitor the smaller monitor normally has scroll bars on it to get from the bottom taskbar to the window header bar, quite annoying. SOLVED 15-Jan-2012: Change both monitors to use the highest common resolution.

 

 

Advertisements

SSRS Configuration – “user … does not have required permissions. Verify that .. and … UAC …

SOLVED

User ‘<server>\<username>’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

“To get this resolved you need to start you Internet Explorer as Administrator -> Run as Administrator then open the url http://<Server name>/Reports this will take you to the “SQL Server Reporting Services” Home page
Click on the “Folder Settings” button.
Click on the “New Role Assignment” button.
Add the Group or user name: DOMAIN\USER_NAME, and permissions you have logged in as.
Click on Ok button.
This should resolve the issue.”

VS TFS Build

SQL Linked server – Change of password

TODO confirm this works then mark as Solved

A linked server using VFPOLEDB has worked for sometime here. Then the server related user password was changed and it all stopped working. Perhaps consider following page

http://msdn.microsoft.com/en-us/library/ms175537.aspx

“Linked server login mappings can be added by using sp_addlinkedsrvlogin and removed by using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server.”

Entity Framework: Stored Procedure – Function import

How to…get a stored procedure from SQL and use it in code
In this case execute only, not entity CRUD

  1. Model Browser 
  2. Stored Procedure
  3. Right-click
  4. Update Model from database
  5. Get your stored procedure
  6. However at this point still unusable in code
  7. Not obvious step > Double click Stored Procedure
  8. Choose function import options
  9. OK creates function import which is then useable in code
  10. Note all parameters are surpisingly nullable – May be there is a way preventing this. Unsolved

 

Enjoy !

Entity Framework: WinForms databinding in EF4.1 – DBSet

Solved

Dim context As New SOPEntities
System.Data.Entity.DbExtensions.Load(context.Employees.Where(Function(f) f.Current = True).OrderBy(Function(g) g.FullName))
EmployeeBindingSource.DataSource = context.Employees.Local
ListBox1.DataSource = System.Data.Entity.DbExtensions.Tobindinglist(context.Employees.Local)
ListBox1.DisplayMember = “FullName”

Problem

‘local’ is not a member of ‘System.Data.Objects.ObjectSet(Of <entityname>)’

See http://wp.me/p17IS4-ih

 

Solved

Open the <Name>Model.edmx > Right click > Add code generation item…

Choose ADO.Net DbContext Generator. This has affect of changing the .edmx file Code Generation Strategy to “None” and creates a text template model and .Local now works.

Discussion

Uses EF4.1 EntityFramework.dll
Database first, then add code generation item to get T4 simpler classes and DBSet inherits DBContext
Initially could not work out how to get the Load method. See above for solution.
There is no MSDN VB.Net sample yet. The C# is more like context.Employees.Local.ToBindingList. So it took me a while to work out the syntax above.

Unsolved:
Also the FullName is now a computed column in the database. Was unable to work out how to do this in EF. It may come in the future as a “Model Defined Function”. Do not think this is a Scalar or Complex Type which is something else.
Unsolved:
For BindingSource to support complex filter it needs to support IBindingListView. Typically EF does not.

Links

Binding Objects to Controls
http://msdn.microsoft.com/en-us/library/gg197521(v=VS.103).aspx

Following is in C#
http://msdn.microsoft.com/en-us/library/gg197523(v=VS.103).aspx

Transact SQL: Dynamic Pivot

Normal pivot in SQL means pre-defining the pivot column output, unlike Excel which dynamically works out the pivot columns.
A solution to this was found here with thanks to:  http://sqlserver-qa.net/blogs/t-sql/archive/2008/08/27/4809.aspx This copied after comments below with my own naming again here.

Comments

I wanted to join the data from this dynamic pivot to other data relating to the item and for one parameter (ProductID). I created a stored procedure which created a dynamic SQL including these columns and the parameter and passed this SQL to the generic dbo.uspDynamicPivot.

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results. Consider this example

select * from
(
    selectYear(OrderDate)as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
    INNERJOIN northwind..Orders as o ON(e.EmployeeID=o.EmployeeID)
) as t
pivot
(
   
Count(OrderDate)for pivot_col in([1996],[1997])
)
as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql

This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post

create proceduredbo.uspDynamicPivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries
varchar(100)
)
as

declare

@pivot varchar(max), @sql varchar(max)
select @select =replace(@select,‘select ‘,‘select ‘+@PivotCol+‘ as pivot_col,’)
 

createtable #pivot_columns (pivot_column varchar(100))

Select

@sql=‘select distinct pivot_col from (‘+@select+‘) as t’

insert

into #pivot_columns
exec(@sql)

select

@pivot=coalesce(@pivot+‘,’,)+‘[‘+pivot_column+‘]’from #pivot_columns

select

@sql=

    select * from 
    (
        ‘
+@select+
    ) as t
pivot 
    (
        ‘
+@Summaries+‘ for pivot_col in (‘+@pivot+‘)
    ) as p
exec(@sql)
 Purpose : Find total sales made by each employee for each year
Usage :

EXEC

dynamic_pivot
‘SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ‘
,
‘Year(OrderDate)’,
‘Count(OrderDate)’

Purpose : Find total sales made by each company for each product

Usage :

EXEC

dynamic_pivot
‘SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid’
,
‘productname’,

‘sum(total_cost)’

Published Wednesday, August 27, 2008 12:58 PM by Madhivanan

Entity Framework: Generating Strongly Typed Entity Classes: Lightweight

Lightweight

Step by step here: http://msdn.microsoft.com/en-us/data/gg685494

Videos here:  http://msdn.microsoft.com/en-us/data/aa937723

How to remove serer name items from history of sql server management studio

 

http://stackoverflow.com/questions/1059105/how-to-remove-server-name-items-from-history-of-sql-server-management-studio

For SQL 2005, delete the file:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

For SQL 2008, the file location, format and name changed:

C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

How to clear the list:

  1. Shut down all instances of SSMS
  2. Delete/Rename the file
  3. Open SSMS

This request is registered on Microsoft Connect

https://connect.microsoft.com/SQLServer/feedback/details/424800/ssms-expose-connect-to-server-mru-list-to-users

 

Microsoft Office Excel: “Change startup workbook” and “Create Templates”

Save As > Book.xltx
Change type to Template >
Save in C:\Program Files (x86)\Microsoft Office\Office14\XLSTART

http://office.microsoft.com/en-us/excel-help/customize-how-excel-starts-HP005199391.aspx