“Must declare the scalar variable” error in Toad for SQL

Started learning how to work with variables in T-SQL. Doing first basic exercise to declare today’s date and then print it, failed mizerably.

I was using Toad for SQL because this is my day to day tool. Tried several other examples, searched online for this error… looks like “it works”, but it doesn’t for me?!

Opened Microsoft SQL Management Studio, pasted same example, hit F5. Boom, worked! Ok, then it’s something related to Toad for SQL.

Searched again with Toad word included. Of course, found the issue description quickly on Toad’s forum: https://forums.toadworld.com/t/data-point-error-must-declare-the-scalar-variable/27407/8

This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.

There are different execution needs depending on what you are doing.

When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.

Or when you have a script you might want to execute all in the script from top down. For this you use F5.

There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.

In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.

Install-WindowsFeature Web-Net-Ext failed. Source files could not be found.

Error:

Trying to install .NET Framework 3.5 Features on a Windows Server 2016, either through GUI or PowerShell, returns below error (truncated):

“The request to add or remove features on the specified server failed.
Installation of one or more roles, role services, or features failed.
The source files could not be found.”

Cause:

.Net Framework 3.5 sources file are not pre-installed so we need to explicitly specify where “installer” can get the source files from.

Solution:

As per Microsoft’s official docs, there are 2 main solutions to make it work.

  1. Through GUI (Server Manager), when asked during the wizard, you need to specify the path to “Media:\Sources\SxS” folder.
  2. Through PowerShell, Install-WindowsFeature -Name Web-Net-Ext -source D:\Sources\SxS

Still, Microsoft says that even if this defined source is not found, installer should go to Windows Update to download required files. Let’s exclude those many possibilities that Windows Update is not accessible due to various reasons (no internet connection, GPOs disabling something etc.).

Anyway, since neither one of the above methods worked for me, I kept reading posts from 2nd+ google pages. This led me to not-so-old post from Michael Niehaus: https://blogs.technet.microsoft.com/mniehaus/2015/08/31/adding-features-including-net-3-5-to-windows-10/

Not sure what’s different with calling DISM directly (since Server Manager and Powershell probably does the same), but below solution worked for me.

Run this from PowerShell (including tilde characters): DISM.exe /Online /Add-Capability /CapabilityName:NetFx3~~~~ /Source:D:\Sources\SxS where D was the drive with my mounted Server 2016 ISO.

When above command is done, run this: Add-WindowsCapability –Online -Name NetFx3~~~~ –Source D:\Sources\SxS

Power BI: The key didn’t match any rows in the table

Error:

Refreshing Power BI report generates “The key didn’t match any rows in the table” error.

Cause:

Click “Edit Queries” button. If you do not see the error message, click “Refresh Preview” button. Once you have the error message, click “Go To Error“.

Error message will remain, but you should have “Edit Settings” button now, click it.

In the next screen, Navigation, you should see what exactly Power BI is trying to access but cannot do so.

In my example, I have an Excel file as my source. When I first connected to this data source, my Table name was Table1. Yesterday I changed where my Excel file gets data from and this, in turn, changed Excel’s Table name to “report“.

Obviously, when Power BI tries to refresh the data, it cannot find Table1 table anymore.

Solution:

In the same Navigation screen, selecting my new Table name “report” will fix the issue assuming all other columns in the Excel file are the same.

Same is true for any data source, not only Excel file. Follow same steps to identify what’s causing the error and then fix it as needed.

Object Replication Manager failed to process Object changes.

Symptoms

Did your SMS_OBJECT_REPLICATION_MANAGER component shows a lot or 6004 MessageIDs with the following description: “Object Replication Manager failed to process Object changes. These changes will be retried on next processing cycle.?

Cause

By design, maybe.

Resolution

Run the following query in SQL Management Studio (Warning! modifying the database directly may not be supported by Microsoft. Do this on your own risk.):

SELECT ca.UniqueID,c.CollectionName
FROM vClientSettingsAssignments as ca LEFT JOIN collections AS c ON ca.CollectionID=c.SiteID
WHERE CollectionName = NULL

This will show collection name/s for all active assignments to collection/s that do not exist anymore.

If you have any, copy the UniqueID and run the following query:

DELETE FROM vClientSettingsAssignments
WHERE UniqueID = ‘{UniqueId here}’

Credit goes to: https://social.technet.microsoft.com/Forums/en-US/2e9ba246-194d-47d4-adc9-eb3b1717bfdc/object-replication-manager-failed-to-process-object-changes?forum=configmanagergeneral

Despooler failed to merge delta to the compressed package. Error 112.

Symptoms

You make changes to a package and update your distribution points. Shortly you receive error messages in Component Status for SMS_DESPOOLER component.

image

Status message details is similar to the following:

image

Cause

The destination drive where site server is uncompressing the package is full.

Resolution

If you don’t know the drive where the package is uncompressing to, open the despool.log file from the site server system where this error occurs. In the log file look for the following lines:

Received package PackageID version 12. Compressed file –  F:\SMSPKG\PackageID.DLT.11.12 as F:\Program Files\Microsoft Configuration Manager\inboxes\despoolr.box\receive\PKG8l1ab.TRY
Old package storedUNC path is \\SiteServerFQDN\F$\SMSPKG\PackageID.PCK.
Use drive F for storing the compressed package.
No branch cache registry entries found.
Uncompressing F:\SMSPKG\PackageID.PCK to F:\SMSPKG\PackageID.PCK.temp
WriteFile failed, 0 bytes written
FileWrite failed; 0x80070070
decompression failed for F:\SMSPKG\PackageID.PCK to F:\SMSPKG\PackageID.PCK.temp
CContentBundle::ExtractContentBundle failed; 0x80070070
Failed to extract contents to the content library. 0x80070070
Failed to extract package contents from F:\PROGRAM FILES\MICROSOFT CONFIGURATION MANAGER\INBOXES\DESPOOLR.BOX\RECEIVE\PKG8L1AB.TRY. Error = 112

So, looks like we have a low disk space problem on disk F:. Clean it up to create more space and monitor Component Status again. Shortly, you should see the following:

Received package PackageID version 12. Compressed file –  F:\SMSPKG\PackageID.DLT.11.12 as F:\Program Files\Microsoft Configuration Manager\inboxes\despoolr.box\receive\PKG0auju.TRY
Waiting for ready instruction file….
Old package storedUNC path is \\SiteServerFQDN\F$\SMSPKG\PackageID.PCK.
Use drive F for storing the compressed package.
No branch cache registry entries found.
Uncompressing F:\SMSPKG\PackageID.PCK to F:\SMSPKG\PackageID.PCK.temp
Content Library: G:\SCCMContentLib
Extracting from F:\SMSPKG\PackageID.PCK.temp
Extracting package PackageID

Extracting content 1c4c0f71-e7a3-4cda-813f-dc7baff8d6a7
Extracting content 93f2ea53-fbf5-4082-98c3-a2b3887dd658
Extracting content 73a4fc00-02da-4e32-824d-957053338834
………
Extracting content 79267f60-69a5-465c-8a40-a6a8db8b1416
Extracting content 1181a482-c797-451c-9468-e2bf1bc8d68d
Package PackageID (version 11) exists in the distribution source, save the newer version (version 12).
Stored Package PackageID. Stored Package Version = 12

This time, our package uncompressed fine and it was stored successfully in the content library.

Status message details will now show this message:

image

Installing Forefront Endpoint Protection 2010 RC – prerequisite error

While installing the latest “Forefront Client Security” product named Forefront Endpoint Protection 2010 (RC version), the prerequisites verification step displayed an error at the “Verifying restart is not required” step. And the error details: “An earlier software installation still has outstanding file rename operations pending. Before Setup can continue, you must restart the computer.”



The more stranger thing happens after you restart your computer and start the Setup again. The same is displayed. Why? Well, maybe because on the FEP 2010 requirement is to have a working Configuration Manager 2007 infrastructure and almost always ConfigMgr will have open files and read/write operations on the computer you’re trying to install FEP 2010.

So, to workaround this problem you have to modify your registry (do it on your own as I did). Open PendingFileRenameOperations key from HKLM\SYSTEM\CurrentControlSet\Control\Session Manager and delete or copy its content to a notepad. Save the key and start Setup now, it should work, at least it for me.

Thanks to Evgeny-D who posted this on Microsoft’s forum. I’m glad that I did not forget Russian language 🙂

Install SQL Server on a 64 bit Windows Server–missing SQLncli_x64.msi

I had to install a copy of SQL Server 2005 on a Windows Server 2003 R2 SP2 x64. During installation I got a strange error saying that SQLncli_x64.msi file is missing. I was pretty sure that I have the same ISO file as I did while installing SQL so many times before and without any errors… Searching the net, the solution from Microsoft forum was the right one.

Before installing SQL Server, you have to download and install Microsoft SQL Server Native Client x64 file (sqlncli_x64.msi) from Microsoft Download Center.

Note: If you’re going to install Analysis Services too, then make sure to download and install Microsoft SQL Server 2005 Management Objects Collection x64 file (SQLServer2005_XMO_x64.msi) also.

Problem installing WSUS on a remote SQL server.

Today I again learned a lesson I knew for some time but was hoping that I can forget it. The lesson was about: “Trust anyone. Test for yourself.”

Having to install WSUS on a remote SQL 2005 that had WSUS database installed before, I asked one SQL guy if renaming the old WSUS database is enough to install a new WSUS DB. He said “yeah, sure!”. Ok then, I started installing WSUS.

Needless to say, installation failed:

There is a problem with the windows installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package.

Two hours of troubleshooting permission and connection problems, made me read all WSUS installation log files located in “X:\Users\username\AppData\Local\Temp”. One of the file was 1 KB size so it does not contain much info. Anyway, the file content was:

Changed database context to 'master'.
Msg 1802, Level 16, State 4, Server SQL-SERVER-NAME,  Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Server SQL-SERVER-NAME,  Line 2
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SUSDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Changed database context to 'master'.

One specific line says it all… “Cannot create file … because it already exists”

I guess when doing something, you have to test for yourself everything. Lesson learned. Again.

Failed to Run Task Sequence – 0x80072EE7

If you receive the following error: “An error occurred while retrieving policy for this computer (0x80072EE7)” when Task Sequence Wizard starts, this can happen if you have DHCP or DNS problems.

You should check if you can contact the DHCP server in order to get an IP address or you can configure a static IP address.


If you have a switch with STP enabled, you might want to read this post.

Failed to Run Task Sequence

I just spent half an hour trying to understand why my task sequence cannot find the files on a distribution point; although the TS runs normally, I see all available TSs and I can choose the one I need. Then I found why: I missed something I never missed before and that must not be forgotten! I forgot to configure the network access account.

So, if you receive the following error: “This task sequence cannot be run because the program files for <your package here> are inaccessible on the distribution point.”, you should check if your network access account are configured and the information is correct.

To resolve this issue, you must correctly configure the System Center Configuration Manager 2007 client network access account. To do this, follow these steps:

  1. Use an account that has administrative permissions to log on to a computer that has the System Center Configuration Manager 2007 Administrator Console installed.
  2. Click Start, click All Programs, click Systems Management Server, and then click SMS Administrator Console.
  3. Expand ServerName, expand Site Management, expand SiteName, and then expand Site Settings.
  4. Click Client Agents, right-click Computer Client Agent, and then click Properties.
  5. In the Computer Client Agent Properties dialog box, next to the network access account that you want to configure, click Set.
  6. In the Windows User Account dialog box, enter the user name and the password that you want to use for the network access account, and then click OK two times.

Here is Microsoft’s article about this: http://support.microsoft.com/kb/935914