SQL Server Compact Edition and Visual Studio

27-Dec-1111 Leave a comment

Motivation

To distribute applications to friends and family

Limitations

  1. No Schemas
  2. No Views
  3. No Stored Procedures
  4. Data types missing include:
    1. nvarchar(max)

Issues

  1. Difficult to switch a dataset from SQL to SQL compact at runtime. Considering having to projects with the datasets one SQL and one SQL Compact. Then having an IObjectData and implementations of both
  2. SQL Server Compact 4.0 is not supported by SSMS SQL Server Management Studio
  3. SQL Server Compact 4.0 is not supported by datasets

Because of limitations of 2 and 3, it may be best to stick with 3.5 SP2 for now. 4.0 seems more linked to Web projects for the minute.

If using 4.0 or in anycase it is possible to use Visual Studio > Views > Server Explorer or also Visual Studio > Data drop down menu item

Tools

Visual Studio Tools for SQL Server Compact 4.0. Unable to find microsoft site but link from here works.
Until this tooling is installed the pre-requisites offers only 3.5 SP2

http://erikej.blogspot.com/2010/12/visual-studio-tools-for-sql-server.html

Error Messages

“Format of the initialization string does not conform to specification starting at index 0” Means that the connection string format is not correct. I had missed out the “Data Source=”

Filed under ADO.Net, SQL Server, Visual Studio Tagged with

VB.Net programming of SQL Server Replication

26-Dec-1111 Leave a comment

The article http://msdn.microsoft.com/en-us/library/ms146869.aspx suggests that Microsoft.SQLServer.Replication.dll is in the .Net references. For me it was not, it could however be found at:
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Also needed:

  1. Microsoft.SQLServer.Rmo
  2. Microsoft.SQLServer.Replication
  3. Microsoft.SQLServer.Replication.BusinessLogicSupport
  4. Microsoft.SQLServer.ConnectionInfo

Filed under ADO.Net, SQL Server, VB.Net Tagged with

Visual TFS Reports setup

23-Dec-1111 Leave a comment

Will not work on Windows 7 – See other post in this blog

  1. Pre-requisites
    SQL Server 2008 R2 with Reporting Services and Analysis Services
  2. Use Team Foundation Administration Console > Application Tier > Reporting
    Edit > Use Reporting ticked > Warehouse Tab > Server (enter name) Database (Tfs_Warehouse)
    Analysis Services Tab > Server (enter name) Database (Tfs_Analysis) Account for accessing data sources Username maybe Domain\User
    Reports Tab > Server (enter name) > Populate URLs Web Service in format http://server:port/ReportServer and Report Manager http://server:port/Reports
    Account for accessing data sources Username maybe Domain\User
    Default Path /TfsReports
  3. May need to Use the WarehouseControlWebService.asmx as in Connect link below. Address is
    http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx
    http://connect.microsoft.com/VisualStudio/feedback/details/653006/team-foundation-reports-broken-the-warning-parameter-is-missing-a-value-iterationmdxparam-parameter-is-missing-a-value
  4. For me the standard out of the box reports for MSF for Agile.. were missing
    This seems to be because I created the “Visual Studio > Team Explorer > New Team Project ” before setting up TFS reporting.
    First attempt in brackets below. My second attempt was “Visual Studio > Team Explorer > New Team Project > this creates reports for the process of your choice. > Move the reports to the project folder of your choice > Detach the project > Go to Team Foundation Server Administration Console > Team Project Collections > Delete the Team Project”

    (Initially tried to resolve this by finding the Compressed Zip file with the standard reports in. Then uploading the reports into Report Server one by one.
    From the source I had, this had the affect of wrong datasources. After pointing the datasource to the new name Tfs2010ReportDS instead of TfsReportDS this then went further)

  5. Bug Status report uploaded send the “Warning” parameter was missing a value.
    First attempt > Manage report > Parameters > Click the web button for Use Default or something like this> Report then ran but had no data
    Second attempt > TFS Administration Console > Reporting > Start Rebuild (takes a while)
    See http://msdn.microsoft.com/en-us/library/cc668753.aspx
    and http://social.msdn.microsoft.com/Forums/en-US/tfsadmin/thread/c8a324ea-c329-41bf-901e-82f464fac4bb

    STILL NOT WORKING. Getting Closer I Feel.

Filed under Visual Studio TFS

SSRS Subscriptions – Email – Windows File Share

19-Dec-1111 Leave a comment

So easy does not need much detail here.
My publish on a schedule to either a file share or by email.
To use Email settings need to set up in SSRS configuration of server.

PROBLEM
Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

SOLUTION
The report credentails were Windows Authentication. For my scenario it was feasible to save low read-only credentials with the report.

Filed under SQL SSRS, SQL SSRS Configuration

Error 3031: Problem in mapping fragments starting at line… nullable column

16-Dec-1111 Leave a comment

Error 3031: Problem in mapping fragments starting at line… nullable column

In SQL I added a column to a table and initially had the column as Not Nullable. I then updated a view to use the new column. I then updated Entity Framework model from the database. I then changed my mind and made the column nullable. I then updated EF model once again and it would not compile. No-manner of updating column Nullable properties worked.

After some reading suggested this was a bug in EF. As a precaution I updated the view in SQL to refresh the schema went to EF and removed and added it again.

Filed under ADO.Net, Entity Framework, Errors, Troubleshooting, Visual Studio Tagged with ,

Crystal Reports run-time error – “Failed to open a rowset”

09-Dec-1111 Leave a comment

Details: HY000:[Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another comand

SOLVED
Report ran in Crystal Reports designer. Problem turned out to be that there was an unlinked table in Visual designer. Replaced the fields from this table with fields in a view, then removed this table from the report and report then ran

USED this entry:
SRS 18th Dec 2012

THIS ERROR Can also be if there are truly multiple start points for your Crystal Report

In this case use two connections.  This could be the same ODBC connection added to the report again. This works as proved 11-Jan-13
See Purchasing\Requirements.rpt

Filed under Crystal Reports, Errors Tagged with

ODBC Crystal Reports ODBC hanging due to Tracing Log file

07-Dec-1111 Leave a comment

Problem on a colleagues machine where ODBC was very slow on Crystal Reports.

We thought problem was due to 32-bit and 64-bit ODBC. Possibly see http://support.microsoft.com/kb/942976

SOLVED
However problem was that ODBC > Tracing > Tracing was on. Stopped it and crystal reports immediately went quicker. The log file was also very big 200,000 Kb.

Filed under Crystal Reports, Troubleshooting Tagged with

SQL Server Report Manager takes a long time to start up

04-Dec-1111 Leave a comment

See post below
May need to go to Programs > turn windows features on or off for IIS Manager

http://www.developmentnow.com/g/115_2005_11_0_0_641257/Report-Manager-Start-Up.htm

I believe the setting you are looking for is the “Shutdown worker processes
after being idle for…” on the Performance tab of the application pool that
the Reports virtual directory is assigned to.
See old post below for details:
[quoted text, click to view]

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue – I’ll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default – On the recycling tab
there is a checkbox for recycling worker processes – it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab – which is the one you are
interested in changing….
See the “Idle Timeout” section and increase the number of minutes to be 8
hours a typical working day – 8*60 = 480 minutes.
Next, to be sure the “morning person” that runs the first report doesn’t get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.

Filed under SQL SSRS, SQL SSRS Configuration, Troubleshooting

VPN gets stuck on “Verifying username and password”

01-Dec-1111 Leave a comment

When attempting to connect the VPN gets stuck on “Verifying username and password” (hangs). After a while it then gets stuck on “Connecting to **.**.***.*** using *” and eventually returns a long message and may attempt to redial. The solution below is for messages containing text including GRE.

HYPOTHESES

  1. Also I was using both a laptop and PC with VPN switching to a couple of companies. It may fail on one machine if the other machine is already logged in.
  2. Possibly following a failed connection, I found that a VPN I have used a lot on Windows 7 had a blank User name: when this was normally filled in.

EXPERIMENT

  1. Hypothesis 1:- Reverse the first machine to log in. This time the other machine VPN started failing and in fact scenario 2, the User name became blank.

SOLVED
Create a new VPN which worked, then delete the old VPN

UNSOLVED
For error message 800 there are different problems. One of these is conflict between two computers. There may be a timeout on the server before another computer may connect, possible 30 minutes. Secondly may need to uncheck “Use default gateway on remote network”, this worked once, did not work several times.

ALSO
There are pages on web e.g. however first try above
http://www.sevenforums.com/network-sharing/90522-vpn-not-accepting-incoming-connections.html

Filed under Errors, Servers, Troubleshooting Tagged with , , ,