Querying MS-SQL database from Excel 1

[ColumbiaDiver]

Hi All,

Hoping someone can point me in the right direction in respects to a question I have in querying an MS-SQL database from Excel 2010.

I work for a large company and we don’t have a method in place to track what users have access to what systems, (350 at last count). So what happens when a user transfers or leaves the company is our security department basically SPAMS every system admin in the company with a e-mail that basically says please check all systems you support to see if these users exist in your system and if they do term their account and if they don’t respond that they were not found.

The problem is I am the system admin for multiple systems and each of those systems have multiple environments, (Dev, Test, Prod, Lab, Sandbox etc). Plus we get a lot of term requests on a daily basis. As I’m sure you can imagine checking 5 environments across 5 systems gets pretty time consuming and to make it worse 90-95% of the time those users don’t even exist in the systems I support. So if I get a request to term 10 users across (20 systems/environments) it gets pretty time consuming, log in look for users, log out, log into next system/environment, look for users etc etc.

The systems all have MS-SQL databases that contain tables that list the user profiles and their status. I was going to write a program to do a read query against the database for a list of terms, but unfortunately ran up against our security policies which would require a lengthy approval process to be allowed to deploy such an application so I am limited to using what we already have.

I created an Excel template that I and my other admin populate with the list of requested terms, which makes it easier, but still overly time consuming.

The template has a column for each system and each environment for each system. What I would like to do is:
1) Populate the template with the list of terms, Col1 = User ID, Col2 =User Name, (this is done by copying the information from the e-mail)
2) Execute an MS-SQL against the database\user table from Excel for each UserID to see if it exists in the system. Then doing the same for each system/environment for example,

Select * from database\userTable where UserId = data_in_Cell_A1 (the user ID) and if you find it then place the word FOUND in the Cell A3 – (SystemABC Dev). Then repeat down the rows for cells A2, A3, A4 etc. placing the respective FOUND or NOT FOUND in the column for A4 – (SystemABC Test) A5 – (SystemABC Prod) A6 – (SystemXYZ Dev) etc.

So I see two possible approaches (1 I can query MS-SQL for each userID in each cell of the sheet or (2 I can export the entire user table to a separate Excel sheet each time you open the template to start a new set of terms then execute a lookup against that sheet.

So far I have been able to query the database against a value in the userID cell and if it’s exists return the UserID and the level of access to the adjacent cells (B1, C1), but I can’t figure out how to easily replicate the query for each of the following row cells with the other UserID's in them, query the database against the values in A2, A3, A4, A5 and populate the adjacent cell with FOUND or NOT FOUND and so on.

I’m also concerned about the possibility of executing multiple queries bogging down the system.


Thanks

Gordon

 

[SkipVought]

HI,

So what's your query? You ought to be able to use...
[tt]
Where UID IN()
[/tt]
...statement, by building an In-String. So the query would return only those accounts that are supposedly active.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColumbiaDiver]

Hi Skip,

Not sure I follow you, but then I'm fairly new to MS-SQL, (I know just enough to be dangerous), an example would be great. I've also attached a screenshot of the test sheet I setup and the template sheet to give you a better idea, (names were changed to protect the not so innocent).

My query is as follows:

SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup
FROM database.dbo.pr_operators pr_operators
WHERE (pr_operators.pxInsName=?)
ORDER BY pr_operators.pxInsName

Thanks

Gordon

 

[SkipVought]

I was thinking about a query report, but I'm thinking that a function that returns a binary value: True/False.

Any of your admins could use it and the function would take two arguments; 1) the UsreID 2) the database. The data would be from the cells on the sheet. How would that be


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

A screenshot is virtually useless!

Where do you address the specific databases for each user?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Okay, flip_flopping again, I think that a query based on multiple UIDs would put less stress on your db.

This is a function to return a string to use in your IN() statement....

Function InList(rng As Range)
    Dim r As Range

    For Each r In rng
        InList = InList & "'" & r.Value & "',"
    Next
    InList = Left(InList, Len(InList) - 1)
End Function

We will address this in the next post after you have placed a QT n your sheet.

So you'll need to execute ONE query to your SQL db in Excel Using Data > Get External Data...

This will place a QueryTable on your sheet that can be refreshed or rerun with a modified SQL string via VBA.

When you have placed the QT on your sheet. You could execute the query you posted using any UID.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColumbiaDiver]

Hi Skip,

OK let me see if I can clarify. Some of what your referencing is going over my head a but I’ll try to give it a whirl.

1. Where do you address the specific databases for each user?
In the test sheet that I created I used DataForm Other SourcesFrom Microsoft Query<New Data Source> I used the SQL driver, then provided the database server name using Windows authentication, setting the default database and table and selecting the columns which results in the query I posted earlier. In the actual query I had a user ID listed. When I queried the table for the user id it populated cells A2 and B2 with pxInsName and pyAccessGroup.

I then inserted a new column which I call UserID (Column A) and then clicked on cell B2, go to data, click on properties and edit the query changing pr_operators.pxInsName=Userid to pr_operators.pxInsName=? When I click OK it asks me where to get the parameter data from and I reference cell A2.

SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup
FROM database.dbo.pr_operators pr_operators
WHERE (pr_operators.pxInsName=?)
ORDER BY pr_operators.pxInsName

I tried to create the function you gave me by going to developer optionsVisual BasicRight clicking on the Microsoft Excel Object to the left of the editor and selecting InsertModule and pasting the code there, but it doesn’t seem to like it. I saved the sheet as an XLSM, when I try to run the function I get an #NAME? error, but then I’m not sure how to use the function.

Sorry if I’m a little thick headed here, but I’ve never worked with Excel at the VB level before and this is all brand new to me.

I hope this clarifies it a little.

Gordon

 

[Andrzejek]

Let me throw my $0.02 in here.
How about a little different approach.

You have a few Systems to check, so you have a list of those Systems:[tt][blue]
System1_Dev
System1_Test
System1_Prod
System2_Dev
System2_Test
System2_Prod
...[/blue][/tt]

And it looks from your example that you ask for the same information from the same table (table name and fields’ names) in those Systems (right?)

You go down this list and for every item in the list you do:
(pretty much build your SQL on the fly with BLUE part from your list)
[tt]
SELECT pxInsName As InsName, pyAccessGroup As AccessGroup,
[blue]"System1_Dev"[/blue] As MySystem
FROM [blue]System1_Dev[/blue].dbo.pr_operators
[/tt]
And take the data returned (in a recordset?) and place it (add it) to Sheet1 where you end up with columns:[pre]
InsName AccessGroup MySystem[/pre]

This way you will have ALL Users with their Access Group info from ALL Systems in one place (on Sheet1) in your Excel.

Then if you want to see if UserABC is anywhere in any of your Systems, just do Find in Excel.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Well I'm interpreting your requirement differently than Andy. I'm seeing that the table you're accessing has ALL the access permissions for all your systems. YES?

But I agree with Andy in this respect, that you can have ONE table in Excel of the structure that Andy suggested (3 columns), from which your pivot report (users in column A and systems in Row 1) can be derived.

The essential issue is getting all the User/Group/System into one table in Excel.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

I came up with my idea after looking at ColumbiaDiver’s example (in the picture attached on 22 Oct 15 04:26):
[pre]

A B C D E ...
1 UserID [blue]System1_Dev[/blue] [red]System1_Test[/red] System1_Prod System2_Dev
2 User1
3 User2
4 User3
5 User4[/pre]

For Column B info would come from:
SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup
FROM [blue]System1_Dev[/blue].dbo.pr_operators pr_operators
WHERE (pr_operators.pxInsName=?)
ORDER BY pr_operators.pxInsName

For Column C info would come from:
SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup
FROM [red]System1_Test[/red].dbo.pr_operators pr_operators
WHERE (pr_operators.pxInsName=?)
ORDER BY pr_operators.pxInsName


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Okay, so you added a QT to sheet. However, it seems that you added the QT to the sheet containing your pivot report. This QT needs to be on a totally separate sheet, into which will be extracted all the db permissions for the users in your list.

So lets establish that the variable UserList will have a Named Range, called rUserList. This can be accomplished via one of the techniques in Formulas > Defined Names. I often use the Create Names from Selection process. Getting to know and understand how to use Defined Names and other new features like Structured Tables will greatly increase Excel's usefulness to you in your daily tasks.

Then in this new sheet, lets call this sheet QueryUsers, manually add your QT as you did previously in your pivot report. It matters not that the query is correct at this point. We're just getting a QT Object inserted into the sheet. We will manage the code content in VBA.

In a MODULE in the VB Editor (alt+F11 toggles between the VB Editor and the active sheet) is where your InList() function should be stored and were the query management procedures will be stored. BTW, just as a test, lits say the in A2:A4 you have...
[tt]
a
s
d
[/tt]

Then lets say that in C1 you enter this...
[tt]
=InList(A2:A4)
[/tt]

What should be returned to C1 is, 'a','s','d'

If rUserList is the Defined Name for A2:A4, then =InList(rUserList) will do the same thing.

So not using these assumptions and names, here is the code in my MODULE

Option Explicit

Function InList(rng As Range)
    Dim r As Range

    For Each r In rng
        InList = InList & "'" & r.Value & "',"
    Next
    InList = Left(InList, Len(InList) - 1)
End Function

Sub GetUserPermissions()
    Dim sConn As String, sSQL As String
    
    'build the connection string
    sConn = ""
    
    'build the SQL string
    sSQL = "SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM database.dbo.pr_operators pr_operators"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE pr_operators.pxInsName IN (" & InList([rUserList]) & ")"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "ORDER BY pr_operators.pxInsName"
    
    'execute the query
    With Sheets("QueryUsers").ListObjects(1).QueryTable
'        .Connection = sConn
        .CommandText = sSQL
        .BackgroundQuery = False
        .Refresh
    End With
End Sub

When you run GetUserPermissions, the QT in the QueryUsers sheet will refresh, returning the latest data in your db.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColumbiaDiver]

Hi Skip & Andrzejek,

Just wanted to touch base to let you know I am still working on/looking at this. Work deadlines have shifted my focus a bit. I'm hoping to spend so more time on it today and hopefully nail it down. I'm accumulating a lot of terms so I need to get this working or do another batch by hand, lol

Thanks for your help.

Gordon

 

[Andrzejek]

When you get something going and get stuck - post it here.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ColumbiaDiver]

Hi Skip & Andrzejek,

Well I finally got back to the terms and was able to get it to work after a manner of speaking. I am pulling the user profile data from MS-SQL and saving it in another tab of the spreadsheet, (it updates each time the template is opened). It's not as versatile as other possible approaches, but for now it works.

However, I have run into another issue. The formula I am using now

=IF(ISERROR(MATCH(A2,Users!A$1:A$21,0))=FALSE,"FOUND","NOT FOUND")

looks for the user ID and if found it changes the value in the cell for a given system to FOUND if it's not found it changes it to NOT FOUND. However they want me to add a 3rd criteria based on the state of several other fields in the table and this is where I'm running into trouble.

The 3 possible states are FOUND, NOT FOUND and UNAUTHENICATED. The unauthenicated needs to be based on the following criteria, I just can't figure out how to code it in Excel speak. (see attached sample spreadsheet).

If value hm15639 in cell A2 is not found on the User sheet in cells A2:A21
then Cell C2 = NOT FOUND
Else
If value hm15639 in cell A2 is found on the User sheet in cells A2:A21
AND value in User sheet in the adjacent cell B2 pyAccessGroup = PegaRules:Unauthenicated
AND value in User sheet in the adjacent cell C2 pyOpAvailable=False
Then Cell C2 = UNAUTHENICATED
Else Cell C2 = FOUND

Thanks

Gordon

 

[SkipVought]

Okay, here's what you need to do.

1) Make your Users table a Structured Table named tUSERS:

2) Your modified formula
[tt]
=IF(ISERROR(MATCH(A4,tUSERS[pxInsName],0))=FALSE,IF(SUMPRODUCT((tUSERS[pxInsName]=Table1[@[User ID]])*(tUSERS[pyAccessGroup]="PegaRules:Unauthenticated")*(tUSERS[pyOpAvailable]=FALSE))>0,"UNAUTHENICATED","FOUND"),"NOT FOUND")
[/tt]
...and if it is indented to show what's happening...
[pre]
=IF(ISERROR(MATCH(A4,tUSERS[pxInsName],0))=FALSE,
[highlight #FCE94F]IF(SUMPRODUCT((tUSERS[pxInsName]=Table1[@[User ID]])*
(tUSERS[pyAccessGroup]="PegaRules:Unauthenticated")*
(tUSERS[pyOpAvailable]=FALSE))>0
,"UNAUTHENICATED"
,"FOUND"
)[/highlight]
,"NOT FOUND"
)
[/pre]

This data is pulled from tables in MS-SQL for each sysetm that is being checked, (simplified for this example) and then teh actual Terms sheet uses this data to determine of the user exists.

This refers to the Users table (I called tUSERS).
You stated that this is a QueryTable resultset. However, it appears NOT to be, unless you did something to the Table to remove the Structured nature of this QueryTable, since a QueryTable IS a Structured Table and what you sent was not???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColumbiaDiver]

Hi Skip,

Thanks a bunch for getting back to me.

The data in the User tab on the worksheet is pulled from a MS-SQL database using a select statement similar to the example below, but since I was uploading an example I just copied a sample of the actual data to the to the User tab since it wouldn't work correctly unless the person looking at the example had access to the database server and instance. I used the Excel 2010 option Data-->From Other Sources to create the query.

DRIVER=SQL Server;SERVER=PegaSandox\crmmain;UID=HM55176;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=DELL-L7;DATABASE=Pega_prpc_dev_sandbox

SELECT pr_operators.pxInsName, pr_operators.pyAccessGroup, pr_operators.pyOpAvailable
FROM Pega_prpc_dev_sandbox.dbo.pr_operators pr_operators
ORDER BY pr_operators.pxInsName

Does the fact that I use the above select change the approach you specified?

Also, I've never created a structured table in Excel. I'm guessing I can Google it to get the steps necessary to accomplish it.

I really, really appreciate the help. As I'm sure you can tell complex Excel formulas aren't exactly my strong suit.

Gordon

 

[SkipVought]

Insert > Tables > Table, will convert a table to a Structured Table.

Your Users table, which apparently is a QueryTable, which already is a ST, needs he Name, tUSERS. Select anywhere in the table, and you'll see a context sensitive TAB pop up in the Ribbon. Select in the Group in the TAB and you'll see a Table Name on the LEFT. You can change it there.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColumbiaDiver]

Skip,

Awesome.

I'm working on it now.

Thanks again. I'll let you know how it work out. I think I need to add a byline with your name on it for the process for all the help ypu've given me on this.

Gordon

 

[SkipVought]

Excel is an incredibly diverse tool with lots of features. I can go deep in a few of them related to data acquisition, reporting and data analysis. There are other features that I don't even know about. Glad to help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

ColumbiaDiver,

I'm not sure if this is something you're still working on or already got a solution wrapped up, but for this and similar figure requests, here is something I've been using more times than I can count.

When I have SQL data I want to get into Excel, I write the query in the most efficient manner I can on the SQL side, and then follow the next steps to get the data into Excel (Using Excel 2013, but I've used this since Excel 2007):
[ol 1]
[li]Data Tab[/li]
[li]From Other Sources[/li]
[li]From SQL Server[/li]
[li]Type the server name in the Server name box at the top and hit Finish (assuming you are using Windows Authentication - otherwise, enter the username and password for SQL server.[/li]
[li]On next screen, select the database your query will primarily pull from (default showing is usually "master")[/li]
[li]Make sure Connect to a specific table is NOT checked.[/li]
[li]On version 2013, you have the option of Enable selection of multiple tables - I've not yet tested that feature, so for this example, I assume to leave it unchecked - that apparently would be a way to build your query within Excel, maybe using MS Query.[/li]
[li]Click Finish (note you did not have to pick a table after unchecking Connect to a specific table.)[/li]
[li]Next you may get a prompt (not the first time, but once you've run this once and then run again for another report from same datasource) asking "You already have a file named ... Do you want to replace it with this one?" Always click Yes unless you know a specific reason not to in advance. For example, could be you custom coded a specific connection file that you use with multiple projects, but I've not ONCE found an instance where this mattered - I always click Yes.[/li]
[li]On the next screen, leave whatever table/object selected or randomly select another - it won't really matter what is selected in the end, and click OK.[/li]
[li]Next screen, click Properties[/li]
[li]On the next screen, which should be Connection Properties, click on the Definition tab[/li]
[li]Change Command type to SQL[/li]
[li]Clear out anything in the Command text box, and paste your SQL code.[/li]
[li]Click OK[/li]
[li]Next prompt says a lot, but most likely you'll just need click Yes[/li]
[li]Now your'e back to the Import Data Prompt. Here you can just click OK unless you want to import only as a pivot table rather than the whole data table.[/li]
[li]At this point, after however long it takes for your query to run, your data should show in a table in Excel.[/li]
[/ol]

I know that SEEMS like a lot, but it's really not once you get used to it. I simply wanted tot try and cover all the different caveats and gotchas that come up in the process. Once you've done it several times, it'll be like second nature. I do think it takes more effort and steps than it ought, but at least it works.

Some great things about the approach:
[ol 1]
[li]No VBA code required[/li]
[li]Anyone with the proper SQL data permissions can run the report - so you can setup a report, say using calculated dates if needed with such as DATEDIFF/DATEADD in SQL, and they can refresh their report as needed. That takes you out of having to pull the same data for the same people week after week, month after month, etc.[/li]
[li]You can set it to auto-refresh if so desired, it's really up to you.[/li]
[li]The table setup is done for you right off the bat, so you've got all the benefits of a table without taking the additional step yourself.[/li]
[/ol]



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57