Archive

Uncategorized

Once again ran into this issue; making a blog entry to retain the fix. I’m using Visual Studio 2015 with SQL Server Data Tools 2015. When creating a Foreach Loop with an ADO.net Enumerator the configuration area is blank.

Emptyadoconfig.PNG

There is no need to uninstall, reinstall, etc etc….  It is “TargetServerVersion” setting in the VS project configurations.  Not sure where to set it in the general VS configuration.

The reference I found with the fix is here.

https://social.msdn.microsoft.com/Forums/en-US/abaea48c-7768-429c-9225-2a8e327e56df/ssdt-foreach-ado-enumerator-issue?forum=sqlintegrationservices

I’m providing more details on the getting to the configuration change, since many DBA’s aren’t as Visual Studio savvy as developers are.

From the Project menu choose your SSISName Properties located at the bottom of the list.

Menu.PNG

Find “Configuration Properties” and set “Deployment Target Version” “TargetServerVersion”.

DefaultVersionConfigVnext.PNG

NewVersion.PNG

There is a warning that did not apply to my project.

vNextWarning.PNG

Of course I had to find out what “SQL Server vNext” means to me in SSIS.  That’s another blog entry but started with exploring SQL Server 2017 and finding how SQL on Linux will be useful.

Advertisements

Created a new SQL Agent job calling an SSIS package in the local SSIS Catalog.

Error Message:  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Error: 18452, Severity: 14, State: 1.

I struggled with this error knowing that I was working in the same domain and using the same AD account for the Agent and SSIS services.  The connection string in the package was set correctly and worked fine in my individual development environment.

The fix; the server reference in the connection manager was an alias that apparently didn’t have SPN’s correctly created.  I think it was an old alias.  Changed the server reference in the connection manager to the server name as it appears in AD and it worked fine.

 

 

There are many scripts in blogs and forums to remove a user from all databases in a SQL Server instance.  I want to keep as simple as possible and just drop the database user account.  This script will error on the databases where the users owns a schema.  I want to know those databases and want to retain the login.  Plus having heard so many warnings about cursors I don’t use them.  Some would say a loop in a WHILE statement is the same thing as a cursor.

Simply put, get a list of databases to check for the user, loop through each, and drop the user if it exists.

DECLARE @Databasename VARCHAR(128)
, @Command VARCHAR(255)
, @UserName VARCHAR(128)

SELECT @UserName = ” — Enter a User

DECLARE @DBList TABLE
(DatabaseName VARCHAR(128))

INSERT INTO @DBList
( DatabaseName )
SELECT name
FROM master.sys.databases
WHERE name <> ‘master’

WHILE (SELECT COUNT(*) FROM @DBList) > 0
BEGIN
SELECT TOP 1 @Databasename = Databasename
FROM @DBList

SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’

PRINT @Command
EXECUTE (@Command )

DELETE FROM @DBList WHERE DAtabaseName = @Databasename
END