I love getting get a laptop, but getting all the software reinstalled and making sure everything works can be trying. Last week, I was lucky enough to get a new one and spent two days getting it setup just right. At least so I thought…. once I started working on it of course, BAM I hit my first road block. Visual Studio using SQL Server Data Tool will not render any reports in the Preview tab.
Let the trouble shooting commence!
- Error Message? No help… gives me nothing useful
- Can I deploy report to SharePoint and View? (We use SSRS Integrated Mode)- Success!! This leads me to believe the issue probably lies on my local machine.
- Test Datasets? Can I return data from my query or store procedure connection in Query Designer – Yes. Did I test all my datasets – Yes
- Should I try to uninstall and reinstall? So I did just that. After 2 hours finally was able to test and guess what NO GO!
- Did I install all service packs to VS? Missed one – so installed and tested,still no luck
—TIME TO TURN TO MY VIRTUAL CO-WORKERS ON TWITTER #SQLFAMILY—-
- Try Running Visual Studio as Admin (suggested by fellow Twitter tweep, Martin Schoombee @sqlmartin) – tried… yep no difference
- Finally was given a suggestion to delete my shared data sources and re add them. (suggested by fellow Twitter tweep, John Morehouse@SQLRUS)
I deleted the shared data source for my report I was testing and re-added it. Hit the PREVIEW and BINGO IT WORKS!
So now to see WHY???
Looking over all my data sources I noticed that any of them that use “SQL Authentication” had the user blanked out. Any reports that had used Windows credentials worked, of course, first 5 in my project were all SQL Authentication, just my luck. So instead of actually deleting and re-adding all 30 shared data sources in my project, I was able to go through and just re-input the SQL user names and passwords.
Double Click on Share Data Source
Go to Credentials
If SQL Authentication re-input user name and password
Click OK
Questions still remained as to why my data sets tested ok and returned data. My guess is that it was using my network credential to connect to the data source upon execution. That’s my only explanation and reason why it didn’t dawn on me to check the shared data source connections. The second question is to why the user names were wiped out. My assumption is that they are locally stored and were not carried over to new laptop.
Since this was an interesting mystery I figured I would do a simple blog, so anyone else that may have this issue can have a reference. Hope it helps.