Import Multiple XML files into Access

So boss emails me and asks hey can you import 2,900 XML files into this attached Access database. Of course I say sure thing, no sweat. I knew that I was not the first person in the world with the need to import XML files into access so was confident I could find something on Google to facilitate.

The first thought I had was just throwing the data in a SQL Server database (boss mentioned SQL Server is a fine destination also) using an SSIS pacakge. I know SSIS has a sweet foreach loop container. Optimistically I fired up the Visual Studio designer, thinking that I would actually be able to figure this SSIS thing out pretty quick.

Wrong. Man I do have a desire to learn SSIS (well I at least tell myself I do) but every time I sit down to actually do it, I get flustered and think of an easier way to perform the task in a VBScript, DTS Package, etc.

I went back to Google thinking there had to be some freeware software out there to help with the import. The closest I found was some shareware version of an multi-file XML import. However with the shareware version you could only work with one file at a time. No thanks.

After uninstalling the shareware software, I stumbled on this article from Technet. This looks way to easy to actually work was my first thought.

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”
objAccess.ImportXML “c:\scripts\test.xml”, acAppendData

That’s it??? Really is it that simple? I fired up my Notepad++ and quickly modified the script to line up with my file paths. Double clicked on the resulting VBS file and poof like magic the XML files were importing. So then I just wrapped a Files Collection around it and whamo, I am watching wscript.exe do the rest. So simple.

Final Script:

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “c:\Scripts\db3.mdb”
set fso = CreateObject(“Scripting.FileSystemObject”)
Set f = fso.GetFolder(“C:\XML”)
Set fc = f.Files
For Each f1 in fc
objAccess.ImportXML f1.Path, acAppendData
Next

Advertisements

Server Role vs Database Role

I needed a little refresher the other day on Server roles vs. Database roles in SQL Server 2000. So I created a SQL User called test and gave him no access. I then opened up a query analyzer window and logged in as my new user. I was able to login successfully. I could see master, msdb, and tempdb in the database dropdown box. I tried a quick query on msdb:

select * from sysjobs

Result: Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object ‘sysjobs’, database ‘msdb’, owner ‘dbo’.

However using the stored procedure  sp_helprotect, I started to determine what access my test user had to each database. From BOL: SP_HELPROTECT: Returns a report with information about user permissions for an object, or statement permissions, in the current database. After running this against a user against on the master and msdb you quickly realize that the default created user has a lot of access.

The default user can glean the following information (and a lot more)

  • Database file location, name, etc from sysfiles in msdb
  • User information from sysusers
  • User information from syslogins. Syslogins is interesting because if you do a SELECT * from it you get the permissions error. Inspection of the output from the sp_helprotect shows which columns in syslogins that you have access to. So just alter your select to select the fields you have access to.
  • Information about objects from the sysobjects table
  • Etc. Etc.

It feels like I am just scratching the surface. In the weeks to come I am going to set aside some time to come up with a security audit technique. Anyway, to continue on with my testing I then gave the user some fixed server roles. Tested some queries, rinse repeat, etc.

Lessons Learned (or re-learned):

  • Do not give sysadmin fixed server role out unless it is absolutely justified.
  • Default users with no access have quite a bit of access to the information about your server/database
  • Used the database roles to give access to specific databases, used the fixed server roles to give access server wide.
  • Be as granular as possible when diving out permissions
  • Use the sp_helprotect stored procedure to audit what permissions a user has. Make sure application users do not have to many permissions.

String search in Stored Procedures

I needed a way to get a list of all stored procedure that contained a TRUNCATE command. We were performing a security audit on one of our applications, trying to determine which users needed what access.

I stumbled on this article¬† from Pinal Dave’s blog and ended running this query:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%TRUNCATE%’

Works like a charm. Also used it to find Stored procedures with DROP

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%DROP%’

Optimizer Hints

Man I really need to spend some time and learn how to use Optimizer Hints. A user reported that a query was running slow. I started troubleshooting the issue by first generating an Graphical Execution plan. The main problem was exposed right away.  95% of the process was a table scan on a table that was 7 million rows.

I then fed the query into the Database Engine Tuning Advisor (DETA). The DETA suggested I added a covering index on the 7 million row table for the two columns in my select. With a little more investigation I realized that the PK for that table covers these two columns. Why is the optimizer not using the PK index in the execution plan?

So I set out to rebuild the index and update statistics. No dice. The Optimizer still used the Table Scan vs. the Index Seek. So I started engaging support for the application and after throwing around a few emails, letting them know I had no idea what I was talking about, they finally sent back a query with the Optimizer Hint WITH (INDEX(Index_Name)).

The query went from taking 30 seconds + (sometimes 2 minutes or more) to executing in just under 2 seconds!! Another interesting fact is that the same query ran in 9 seconds on a test server, while using the Table Scan. However that server is a lot more beefier with faster procs and a lot more RAM.

So anyway, all that to say this, I really need to look into Optimizer Hints. Here is a link from http://www.sql-server-performance.com with some more optimizer hint information.