There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database. This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case.
This is a common problem with users accessing reports. There is a way to stop the query from running in the background by using the KILL command, however you will need a DBA to do this for you unless you have permission to do this your self.
Hope this helps other who are struggling with the same issue 🙂
Recently I was trying to send a file via Email in SSIS.
For the connection to the file I was using a variable as the file name changes everyday.
This was causing an issue because it validates the whole package before the file gets generated and the package would fail.
The simplest solution was to delay the validation on the Send Mail Task. You can do this in the properties by setting Delay Validation to True.
Hope this helps you!
After restarting a server that has MDS on, I could no longer access MDS via the browser. This seemed very strange as I have restarted the server multiple times and it had never done this before.
Turns out the Application Pool had stopped for MDS. So I started it up and then it would stop again, frustrating me further.
Then is occurred to me that it was using my domain details and my password had changed. Then I went into the advanced settings of my MDS Application Pool to update my details under Identity.
And there we go, it works and I can access MDS again via the browser!
Hope this helps someone else with this error.
A while ago I was having some issues with SSIS and moving files. There was a hidden file called thumbs.db
When SSIS was running the move file task it would fail because it can’t place duplicate files in the same location. The thumbs.db file was the problem. Even though it was hidden as a windows file, SSIS still saw it and wanted to move it.
This is where limiting SSIS to a specific file type saves you from this problem.
To do this you filter on the Foreach Loop Container. You select the container and then you click on Properties.
In the Properties right at the top you shall see FileSpec. There you can set it to the file type you would like to filter/limit for.
And that’s how you limit SSIS to a specific file type!
Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance.
Got the following error when trying to run my ETL jobs or when trying to deploy the jobs:
“the path for isserverexec.exe cannot be found”
Looking for solutions online, they recommend going into the registry. Which didn’t work for me.
I ended up using the install file and running it to add features to an existing connection. And there is was Integration services was not installed for the instance that I was getting the error with.
Installed SSIS on the current instance that needed it and all worked perfectly after that and the jobs ran as perusal.
Hope this helps someone else in the future.
When trying to install SQL, Windows Update KB2919355 is a prerequisite, and on a new server this is not always installed and the SQL install stops until it is rectified.
Occasionally when you try and install the update you get the error KB2919355 is not applicable, yet the update is not installed. After searching the internet high and low a solution was found.
If you install Windows Update KB2975061 first and restart the machine and then install KB2919355 and restart the machine again, the SQL install prerequisites will be successful and you can continue with your install.
Hope this solution helps you and you get less grey hair and frustration trying to figure it out.
Before you can deploy a SSIS project you need to set up SSIS in SQL Server Management Studio. When installing SQL you will select to install SSIS, however, it does not setup the SSISSDB or configure any settings for SSIS.
Here are the steps for setting up SSIS in SQL Server Management Studio.
Right-click on Integrations Services Catalogs and select Create Catalog.
Check the box next to Enable CLR Integration and set a password for your SSISDB and click Ok.
You will then see you SSISDB listed under Databases and under Integration Services Catalogs.
Now you will be able to deploy your SSIS project to your server where you have set up SSIS.
The next post will be on deploying an SSIS project.
Have you ever worked with code that is impossible to read and decipher? You could use SQL Prompt which will format the code into a readable format, however what if there is so much going on you are still unable to decipher what is happening?
Recently I came across this problem where a function was doing so many things I couldn’t tell where it started and where it ended. There is an amazing tool that was super helpful, it is the T-SQL Source Code Unscrambler that is offered by GenesisOne.
This was a lifesaver for me, saving many hours of trying to figure out code. This tool allows you to connect to your SQL Server database and view any tables, views, stored procedures and functions.
This tool allowed me to select the function that was a mess and in the diagram view I could see what the function was trying to do. It shows if there are conditions, variables and the flow of the function.
This tool is also brilliant for doing technical documentation with diagrams of the systems and data flow in stored procedures and functions.
Below are a few screen shots of the different views and information this tool provides:
Stored Procedure View:
Graphical View of Dependencies:
How to Export:
For a full list of all the amazing features that comes with this application, click here.
Hope you all decide to try out this tool and then get it after you realise how much time it shall save you in development!
Recently a colleague and I had a discussion about which method is the best to use for backing up MDS (Master Data Services) for disaster recovery. We came to the conclusion it all depended on the SQL environment version you were recovering to. Below are the 2 different methods for backing up an MDS database.
- Fast to create, can backup specific versions
- Will always be able to deploy a model to a MDS install
- Can be automated via SSIS
- Can be restore to a server version that is +1, 0 and -1
- If you want to save backups of different models, then the models will have to be backed up separately.
- Security is not retained; it will have to be redone. Can take about 2 minutes per user. On a small model this should not be a problem, however, when scaled out it could be very time consuming.
- Saving more than one backup of a model would require a script/maintenance task to remove backups older than X amount of time.
When deploying the model, the option DeployClone must be used, not DeployNew. DeployNew will remove the MUID and therefore will not allow any changes to be deployed to that model at a later stage. DeployClone keeps it all intact and will allow for future updates.
- Security is retained
- Simple to set-up and maintain
- Easy to restore
- Maintenance tasks to clean up old backups
- Cannot be restored to a server version less than the current version.
The database backup for MDS can be scheduled just like other backups. You can use the maintenance wizard to set up the backup and clean up task on a schedule.
If you know what version the disaster recovery environment will be on, for example the same version as the current MDS server version or higher, then it is simpler to use the Database Backup method. If you are uncertain of what version the disaster recovery version will be then you would want to use the Model Backup method in case you have to restore to a version less than the current MDS server version.
Hope this helps you to choose what is best suited for your environment!
Special thanks to Janlo Du Toit for the discussion on these methods 🙂