I’ve done a few blogs on SQL Server CE (Compact Edition) because I think it’s cool. I do not believe that it’s an enterprise-class relational database, but sometimes you may not need SQL Server Enterprise Edition to handle a given task. It’s free, and it doesn’t require a large or lengthy install. There are some limitations, but limitations are a function of the requirements of the task at-hand; I encourage anyone to check out all versions of SQL Server offered by Microsoft to match the job with the correct database version. Ok enough BS. I was looking for some sort of loop construct in CE, googled for awhile, looked in SQL CE Books Online, but didn’t find squat on how to do a loop in TSQL. So for now, there ain’t one. I see some perf tuning stuff in c#, but I’m doing TSQL here. Below is my transactions/second speed script converted to SQL Server CE. I’ve used a version of this script for over 12 years, and that’s on a lot of servers. I have other blogs around here somewhere, just search for speedscript. It’s nothing special, but it’s accurate – just open up perfmon and crack open the Transactions/second counter, and see how close it is. Now, enough BS again already.  Take this thing and run in in a SQL CE database to see the transactions/second that it produces. Then go to your R900 or whatever and try it out for comparison.  The script is below in a link, but here is the way I created a loop in SQL Server Compact Edition. DROP TABLE COUNTER; CREATE TABLE COUNTER (id decimal (7,2), nBeginTime datetime, nEndTime datetime); INSERT INTO COUNTER (ID) VALUES (25000);   UPDATE COUNTER SET nbegintime = GETDATE(); go INSERT INTO COLLATERAL2( collateral_sak, servicer_sak, pool_sak, collateral_key, borrower_name, borrower_name_idx, track_location_code, home_track_location_code, exist_status_code, production_stage_code, product_code, document_set_code, ctrlnum, casenum, investor_collateral_key, lnamount, firstdue, maturity, closedate, bookpage, recorded, rate, pi, address, address_idx, city, state, zip, armindex, anncap, lifecap, collgroup, instrument_num, armadj, armconv, armround, armmargin, alternate_id, release_request_identifier, bar_code, mers_min, active_flag, major_count, minor_count, is_mom, armfloor, arm_lookback, armindex_rate, curr_upb, mers_register_flag,udf_nchar1, udf_nchar2, udf_date1, udf_date2, udf_dollar1, udf_dollar2, udf_pct1, udf_pct2, udf_int1,udf_int2 )VALUES( 1, 0, 188, convert(nchar(13),1), 'borrower_name',convert(nchar(15),'borrower_name'), 'PEND','PEND','PN','FC','COLL','GNMA','1234','1234567890123','1234567890123',89768.00,'2001-03-07 00:00:000', '2031-03-07 00:00:000','2001-03-01 00:00:000',NULL,'2001-03-01 00:00:000',7.25,675.42,'123 Address',convert(nchar(15),'123 Address'), 'The Big City','NY','91234',NULL,NULL,NULL,'GROUP','INSTNUM',NULL,NULL,NULL,NULL,'ALT ID'+convert(nchar(7),1), NULL,NULL,NULL,1,0,0,0,NULL,NULL,NULL,0.00,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ); GO 25000   GO UPDATE COUNTER SET nEndTime = GETDATE(); SELECT id/DATEDIFF(ss,nBeginTime,nEndTime) FROM COUNTER; GO .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Since there are no variables in SQL Server CE, and I’m finding that you can’t use subqueries unless they return a scalar value, I’ll get back to how to remove the hardcodes; in the meantime, try this and see what you come up with. Oh, and the speed? About 150 transactions/second.  On my cluster, a modest server class box at home with a decent disk io subsystem…750-1000 transactions/second. Lee   ---------------------- SpeedscriptCE

Posted in: Etc. (Off-topic)  Tags:
You might get the following error when trying to connect to a SQL Server CE edition on a network share SQL Server Compact does not support opening database files on a network share. (System.Windows.Forms).     So much for me trying to put my SQL Server CE database on my cluster her at home.  One interesting thing…you can’t connect to it via a network share, but you can create it on a network share and connect to it via the node that you happen to be on. WTF? Below, I am on one of my cluster nodes and have a SQL Server Compact Edition database open on my network share (M:).  Let’s dork around with this some more. I’ll get back to ya. Lee   --------------------------

I see a bunch of emails flying back and forth concerning the PASS board member appointments. If you want a list of interesting blogs for reading, check out the below as supplied by PASS.  Of course I would have like to have seen Sri get an appointment, but I’m not involved in all of that stuff, haven’t read up on it, and therefore wouldn’t want to comment on the good or bad of the elections and appointments.  I like PASS and what they do for the community so I’m sure that they’ll figure it out, modify the rules, throw out the old ones, or something in between. Sort of interesting reading I guess…   From the Blogosphere: PASS Board Appointments The recent PASS Board appointments created much discussion amongst the PASS membership and SQL Server community. We’ve gathered up a list of blog posts dedicated to the topic below. The PASS Board will be discussing the Board appointment process during the Board meetings happening in Seattle this week. · New PASS Board Appointments - Bill Graziano · Ethics and Power - Steve Jones · PASS: Time to Change the By-Laws - Andy Warren · PASS: Time to do a lot more than change the by-laws - Chris Webb · PASS Board Appointments – Additional Explanation - Thomas LaRock · A quick reaction to the PASS board appointments - Aaron Bertrand · Is the problem with PASS, the community? - Simon Sabin · PASS Board Appointments - Response to Feedback - James Rowland-Jones · News Flash! PASS Board Makes Unpopular Decision! - Wes Brown · PASS Board Brouhaha BS Bulletin - Brent Ozar · Thoughts on the Latest PASS Fracas - Audrey Hammonds PASS Ponderings Part newsequentialid - Mike Walsh   If you have some time to kill, go check out these opinions. I don’t right now but will catch up on it this weekend.   Thanks, Lee   ---------------------------     References PASS Community Connector Newsletter (2012). PASS.

Posted in: Etc. (Off-topic)  Tags:
Lee posted on January 11, 2012 12:00
TFS was just installed at my company, so I wanted to go out and take a look at the TFS SQL Server database jobs for database backups, transaction log backups, and anything else I could find. So I crack open SQL Server Management Studio (SSMS) on the server and start snooping around…to find nothing. No maintenance plans, SQL Agent jobs, nothin’. I immediately thought to myself “Oh no, they’re using task scheduler to handle it”. Surely not, right? Surely yes they are. <cough hack belch>. Manage the TFS SQL Server databases through the Team Foundation Administrative Console and not through SSMS for backups. I would imagine that creating maintenance plans for indexes and statistics, or any other job that you want, would probably be Ok.       Lee   ---------------------------  

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
lee posted on January 10, 2012 20:35
I’ve created little utility functions for over 10 years now and I can honestly say that I don’t have a single one saved off somewhere to look back to. Actually, since I wrote one, they’ve changed the objects, methods, properties, etc. so much that it wouldn’t matter. So I’m dorking with scripting a little bit, and I’ve always enjoyed it. Since I am doing more DBA type stuff these days, I want a .exe to script databases on a number of servers daily and save the files off. I always like to have files handy just in case. Here’s one that I wrote today; I’ll be posting a few more of these as I play around with them and make them more robust. My idea comes from the fact that when you script in SQL Server 2008 R2, you can’t save the wizard screens! And, because we have encrypted stored procedures, I have to remember which ones to exclude because if you don’t uncheck and leave those out, the script wizard bombs out.  Here’s a little code that you can try or make better. using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Sdk.Sfc; using System.Collections.Specialized;   namespace Script { class GenerateSQLScript { private static ScriptingOptions Options() { ScriptingOptions options = new ScriptingOptions();   options.ScriptBatchTerminator = true; options.Triggers = true; options.PrimaryObject = true; options.IncludeHeaders = false; options.ScriptData = false; options.IncludeIfNotExists = false; options.ScriptSchema = true; options.ToFileOnly = true; options.AppendToFile = true; options.WithDependencies = false; options.ScriptDrops = false; options.FileName = @"\\" + Environment.MachineName + "\\c$\\temp\\Script_" + string.Format("{0:yyyyMMdd}", DateTime.Now) + ".txt";   return options; }   public static void Main(string[] args) { Server srv = new Server(); Database db = srv.Databases["AdventureWorksDW2008R2"]; Scripter scrp = new Scripter(srv); scrp.Options = Options();   foreach (ScriptSchemaObjectBase Type in db.StoredProcedures) if (!((StoredProcedure)Type).IsSystemObject && !((StoredProcedure)Type).IsEncrypted) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.UserDefinedFunctions) if (!((UserDefinedFunction)Type).IsSystemObject && !((UserDefinedFunction)Type).IsEncrypted) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.Tables) if (!((Table)Type).IsSystemObject) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.Views) if (!((View)Type).IsSystemObject) scriptObject(Type, scrp); }   static void scriptObject(ScriptSchemaObjectBase Type, Scripter scrp) { Urn[] smoObjects = new Urn[1]; smoObjects = new Urn[1]; smoObjects[0] = Type.Urn; scrp.EnumScript(smoObjects); } } } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }   Thanks for reading, Lee   ---------------------

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
Lee posted on January 10, 2012 10:59
PASS has another event that they’re sponsoring, and it’s called SQLRally. Here’s a description: PASS SQLRally regional events fill the gap between the free 1-day PASS SQLSaturday training events and the annual PASS Summit, the world's largest conference for the SQL Server community. At SQLRally, you'll meet with industry experts, authors, Microsoft MVPs, and other people just like you to learn about best practices, effective troubleshooting, and how to prevent issues, save money, and build a better SQL Server environment for your company or clients. An event scheduled for Dallas Texas will be on May 10-11, and they are expecting roughly 500 attendees. Also, and early bird $299 special available until Jan 15, 2012, or if you like submit an abstract to speak at the event. Check back at their site for upcoming events near you. Sounds like a fun event! Lee   ----------------       http://www.sqlpass.org/sqlrally/2012/dallas/Register.aspx http://www.sqlpass.org/sqlrally/2012/dallas/About.aspx http://www.sqlpass.org/sqlrally/2012/dallas/Speakers/CallForSpeakers.aspx http://www.sqlpass.org/sqlrally/2012/dallas/Speakers/CallForSpeakers.aspx  

Posted in: Etc. (Off-topic)  Tags:
lee posted on January 1, 2012 08:55
It’s been a great year, and I am looking forward to an even better one yet in 2012. I want to thank you for your support, emails, replies, and visits over the past year. Wishing you and yours a happy, healthy, and prosperous New Year!  Best of luck in 2012. Lee Everest   -------------------------

Posted in: Etc. (Off-topic)  Tags:
I ran across a blog on federations with SQL Azure, and then happened on a cool video that shows how easy it is to create a federation. More importantly I guess, the video shows all of us where Microsoft is in all of this cloud stuff, and gives us a feel for the features and how far along they are with the technology.  I tried to embed the vid but it’s a Silverlight, so I skipped searching for it and just enclosed the link. It’s below. I do recommend the vid. Pretty cool stuff! Think I’ll go dork with one now. Lee   ------------------- Love this scale-out stuff, but wish all of the splitting and whatnot was automatic     http://www.microsoft.com/en-us/showcase/details.aspx?uuid=955b52c5-3784-4594-a9ff-104491ac5cc0 http://blogs.msdn.com/b/windowsazure/archive/2011/12/13/building-large-scale-elastic-database-tiers-with-sql-azure-introducing-federations.aspx

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
lee posted on December 24, 2011 19:25
Hope you have a Merry Christmas. If not celebrating, my wishes for a happy holiday season to you and your family! Lee   -----------------------------  

Posted in: Etc. (Off-topic)  Tags:
Another year behind us I’d like to take the opportunity to say thank you to all of the folks who happened to have stumbled upon my site over the past year, and I appreciate the emails, feedback, and comments.  When I started blogging a few years back, I had originally used my site to post assignments and lectures from my classes at North Lake College. I quickly found that I really liked jotting down things that I had worked on throughout the day working with SQL Server, so I started blogging.  It’s been nice to receive a note from someone thanking me for helping them solve a problem. Here are a few of my thoughts and feelings about our landscape as we work with our great product and head towards Microsoft SQL Server 2012. Job/Work, and stuff I’m not consulting anymore, and am enjoying the good life. Consulting is great, but it gets old after awhile. Nice to have a place to call home. I’m working at a small company in the Dallas/Ft. Worth Metroplex, and enjoy casual dress every day, a group of cool developers to work with, and bleeding-edge technology. I’m the only database guy here, so I get to do what I like, which happens to be anything from performance tuning, to SSIS, to Mirroring…really, just about anything in the database space I guess.  The devs are also going to give me some assignments programming middle-tier business objects in c#, something that I’ve always found fascinating. Blogging and content at my site Blogging has been fun again as always.  I enjoy adding posts and hope that I am able to help someone out somewhere down the road. The traffic on this blog is incredible, and I want to thank you for stopping by. Send me an email and loop me in on what you’re working on…I’d be interested in hearing of others’ experiments or proof of concepts. As I am always quick to point out, I am no self-proclaimed expert. I’m not a Microsoft MVP or MCM now, and probably never will be. I don’t hobnob with the guys over on SQLBlog.com, nor am I personal friends with the pros over at SQLSkills.com.  I was an acquaintance with a guy up in Redmond on the SQL engine team, but I think I bugged him too much with so many questions that he quit answering my emails. I speak some at events, but haven’t in the past couple of years. I am, though, a SQL Server guy with about 15 years experience, including 8 years with a global app-dev shop building some really great database applications for large companies around Dallas. I also like to geek with SQL Server and always enjoy expanding my own ideas or those of others. Not everything you read here has been proven-out, so take the ideas at face value, and test and expand on them within your own environments. Observations and Outlook SQL Server guys are still very passionate about what they do no doubt! I get a kick out of the messages in forums, and emails in the SQL users groups. I hope that for this next year we continue to take that compassion and excitement and build for the good of the entire SQL Server community. We have a great group among us. Goals for 2012 Here’s my short list of goals for 2012: Get better at SSAS and improve on what I learned in 2011 Pass the SQL 2012 certs when they come out Make it to some the SQL Server User Group meetings in Dallas Blog lots on SQL Server 2012 Work more with Hadoop Virtualize my entire network. I just got my Active Directory box on a .vhd :) Build a server with 32 GB RAM :) Things are getting better each day For those readers allowing me to come into their domain, I am thankful for the opportunity and appreciate your stopping by. I wish you and yours the best in 2012 and hope to meet you down the road at an event. Thanks for reading, Lee Everest   ----------------------

Posted in: Etc. (Off-topic)  Tags:
Download a free eBook available from PASS, the Professional Association for SQL Server. It’s located here and looks to be chock full of good stuff, excerpts from volumes 1 and 2 of the full deep dives books. It’s 96 pages that comprise eight chapters selected from the original books. If you like the information, then you may want to go ahead buy the others for your toolbelt. Lots of “heavys” within the SQL world who author the chapters, some top-notch SQL folks for sure, so I’m confident that the reading is good. I’ll be downloading the freebie and checking it out. Lee   ---------------       http://www.manning.com/passbook/

Posted in: SQL Server 2008/R2 , Etc. (Off-topic)  Tags:
lee posted on December 17, 2011 08:02
Extreme Data Systems has a great tool for searching SQL Server, a free download at their site. I’m not talking about searching for column names, object names, or table names, but rather searching for actual data within tables. SQL Locator seems to do a pretty good job with finding data so I thought I’d give them a plug. Rather than do some screen shots, videos, or whatever…just a plug here. Those other medium are available, just go Google them up. A buddy mentioned the tool to me; the reason we needed it was because neither of us know the database that we’re working with, and actually backed into the correct set of tables to find a problem via searching for a primary key, which happened to be a GUID. Come to think, that’s the only time that a GUID has actually helped me! Anyway, we backed into our tables and then were able, via the nicely named FKs, join the tables and figure out our problem. Check out SQL Locator and see if it works for you. As always with a tool such as this, be careful when using it against production data. Windows Authentication allows it to be installed on an alternate machine and point to production, but I’d still be on the cautious side because it does some crunching for sure. Thanks, Lee   --------------------     http://sqllocator.com/

Posted in: Etc. (Off-topic)  Tags:
I’m reposting a social.msdn.com question that I found…all of the developers that I work with are changing their colors in VS 2010 and (trying to) in Management Studio, going with the old mainframe-ish black background and mostly yellow or white fonts. Pretty cool these app-devs are. So one of my colleagues asked me about saving changes in SSMS, and I told him that I don’t dork much with the colors, but a quick search I found a nice post here.  Simply follow what “Scott M” says and you’ll be good to go: Figured this one out.  Once you set your colors to your liking in S.S.M.S you can back them up via the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors Marked As Answer byscott_mMonday, November 15, 2010 7:39 PM Edited byscott_mMonday, November 15, 2010 8:23 PMtypo Find the registry key above, hit export, and this will include all of the registry keys below the folder. Move the file to wherever your destination is, double-click on it, and it will add it to the registry of the new computer. Your new settings will be transferred to the SQL Server Management Studio. Here’s mine.  …Mmmmmmmmmmm, no. I’m going back to the old.   Thanks for reading, Lee   ----------------------       http://social.msdn.microsoft.com/Forums/en/sqltools/thread/00552b92-3d47-477e-aafc-fb2480519fb3

Posted in: SQL Administration , Etc. (Off-topic)  Tags:
This is a bit off-topic for sure, but I was just sent a Robert Half Salary guide for 2012 that I thought was interesting. It’s located here or at the link below.  Check it out and see where you fall, or where you might want to be, salary-wise. There is also some good stuff about what’s hot, certifications, and some salary variance information depending on geographic location. Why they called it a ‘guide’ and not a ‘survey’ is a bit strange. I don’t know, I thought it was kind of a neat thing I guess. I’ve pasted the page with database folks, including managers. Lee   ----------------------- Don’t know how these idiots who attend meetings, draw pictures, & shuffle paperwork make as much as they do.     http://www.roberthalftechnology.com/FreeResources

Posted in: Etc. (Off-topic)  Tags:
Lee posted on October 21, 2011 06:52
Roughly a year and a half ago, I wrote a quick, minimal blog entitled Geeking with MongoDB and .Net which promptly set all sort of records on this site for traffic in one day; lots of developers, database guys, and others were interested in MongoDB and the NoSQL movement. And they still are. I received lots of emails and replies about it, most were nice, some neutral, and some claiming that my test was bogus…and some were flat-out hate emails suggesting that I should be banished from the world as we know it today. Heck I even got mentioned on a few blogs, one particular on blogs.lessthandot.com stating that I had a flaw in the comparison of a MongoDB insert vs SQL Server. We’re a very passionate lot aren’t we? It was definitely an interesting week, no doubt. Which begs the question, why is NoSQL labeled a movement? To me that’s a bad descriptor, more like a moniker than what is actually happening today. A movement by definition is defined as “the act or process of moving” or a “particular instance or manner of moving” which incorrectly describes NoSQL in my opinion. Was it a movement when XML showed up? I mean, did we all go solely with XML and completely do away with .txt files for storing data as text? No. Or, was it a movement when the SQL CLR showed up? Um, no again. These additions, in fact, were enhancements to our great product and landscape; an addition to our toolbox which may someday allow us options to approach a problem in order to find the best solution. There was no mass exodus, as there will not be with NoSQL. The “movement” verbiage is inaccurate; I view MongoDB and NoSQL as an addition to how we go about crafting solutions. And it’s not going away. I also see where my friend Bryan Smith of Microsoft and the Microsoft MTC South Central District in Irving, TX recently spoke on NoSQL at the North Texas SQL Server Users Group meeting, recently as in last night, about this very technology. (I couldn’t go, but I’ll check back…they’ll probably add his presentation and code there soon, or he will on his blog). Interesting eh? Now, why do you suppose that someone from the mother ship is doing lunch and learns on NoSQL? Surprising to you? Naa, not so much to me anyway. Microsoft is using this technology in more places than you think. I’m continually amazed each time I meander over to the MongoDB site to see growing number production deployments. Amazing! Anyway, I could write more on this, but back to why I’m writing this morning…I’m going to the MongoDB event in Dallas on November 17, 2011 and am excited to see what’s up with the current version of MongoDB. So fam, come out and join if you want, and learn about this technology. I’d like to meet you while I’m there as well. I haven’t set up a shard yet on my servers, but I’m jazzed to learn more about it and see it in person so I can set one up here at home. Who knows – maybe one day I’ll be setting up one at work. Thanks, Lee ---------------------- http://www.merriam-webster.com/dictionary/movement http://www.10gen.com/events/mongo-dallas-2011 http://en.wikipedia.org/wiki/NoSQL http://blogs.lessthandot.com/index.php/All/ http://blogs.msdn.com/b/data_otaku/

Posted in: Etc. (Off-topic)  Tags:
I’ve been thinking about PASS a lot this year…mostly because I didn’t get to go. If you’re not knowledgeable of PASS, it’s the conference of conferences for SQL Server practitioners, DBAs, developers and anyone who writes a SELECT statement that’s held in Seattle, WA (most years), and put together by the Professional Association for SQL Server. My employer didn’t budget any money for either of the three of us on our team to go, apparently, so we get to miss out on a really great learning experience and get-together of anyone that’s anyone in the SQL world. I’ve been before, had a great time, and learned a ton. Hate to have missed it this year, and this was not a good year to miss, considering that SQL Server 2012 (formerly Denali) will be released soon. I wanted to make a list of reasons why a SQL guy should attend, or some other writing to maybe go convince your boss to send you or whatever; no reason to, though. Just go out and google up reasons to attend PASS. They’re well documented and all over the internet. So, I decided to do a little different search. I dialed up “Is SQL Pass a waste of time?”, “Is PASS worth the money?”, “Is PASS worth it?” quickly…I may very well have missed one, but I didn’t see one post that indicated anything such. PASS is no doubt a great learning experience, and not many complaints exist out there. Attendees truly enjoy PASS – going to courses all week from top SQL Server guys, Microsoft employees, and vendors is a great learning experience. The camaraderie of making friends with other SQL Server geeks is also very enjoyable. Fact is, PASS is worth it.  I see on their site that PASS Summit 2011 Opens with Record 5000+ Registrations this year. 5000! Wow, that’s huge! Damn, do they have enough hotel rooms out there? Apparently someone thinks it’s worth the bucks, right? Some folks even pay their own way to PASS – maybe I need to do this next year. I figure that if you get a cheap flight and make 10 stops if necessary, stay at a Holiday Inn Express, and eat bugs for supper, it would cost a person maybe 3K depending on where they live. I’ve actually offered to pay for 1/2 of the cost to a previous employer, but companies seem to get funny and act weird when you offer this. Maybe because it makes the company look like a bunch of cheap b@stards. I’ve thought of making a family vacation out of it or SQL Server Connections since it’s in Las Vegas, with PASS Summit the better of the two by far, but haven’t yet. I’ve also often thought of submitting a session – I have enough topics in this blog site to scrape up something together that’s of interest. Or I could hack up some cool project maybe and submit it, and with almost 10 years speaking in front of students, I’d really enjoy the teaching experience. Maybe I will next year. It might be the only way I get a trip to PASS paid for. Lee   ----------------------       http://www.made2mentor.com/2010/10/why-im-paying-my-own-way-to-pass-summit-2010-and-you-should-too/ http://sqlpass.org/ http://www.sqlpass.org/summit/2011/Live/News/Partner25.aspx

Posted in: Etc. (Off-topic)  Tags:
I’m doing an interesting project in work where I am “scraping” data off of word documents, and may do a few blogs on it. Probably not the best way to handle this sort of thing, but it works well without the cost of a third-party tool that transforms data to XML, and the code is fairly straight forward. In my task, the data from a series of .doc and .docx templated files will be moved to a .csv, imported to SQL Server, cleaned, moved to a dimension table, and finally, fed to a cube in Analysis Services. Pretty cool eh? I think so. One thing about doing this is that you’ll have to have either 1) Microsoft Office installed on the server, or 2) the Primary Interop Assemblies Redistributable, or PIA, installed on the server, both so that you can take advantage of the Office (in my case Word) methods to fetch the data off of the documents if you plan on scheduling and running from the server itself. Most servers normally don’t have Microsoft Office installed – there’s no reason – and to avoid burning a copy of office that costs money, you can add these assemblies…the download is free. It reminds me back in the day when we used SQL Mail, going back to 1998 and SQL 6.5 and then SQL 7. You had to install the entire Outlook client on the server, and then a smart DBA would go and randomly delete the GUI so nobody could open the client.  Just a random thought…didn’t SQL Mail really suck back then, and now? For those of you who started as a SQL practitioner beginning with SQL Server 2005 and only know database mail, you really missed a lot of fun trying to figure out why that abomination never worked correctly. Oh well. Anyway, get the PIARedist.exe, and start doing some slammin’ Office Automation. Happy Primary Interopping! Lee Everest   -------------------------------- http://www.microsoft.com/download/en/details.aspx?id=3508

Posted in: SQL Server 2008/R2 , Etc. (Off-topic) , .Net  Tags:

by Lee Everest, M.S.

Poll

Do you use Azure or cloud in your organization?
Show Results

Ads

Search


Month List

Calendar

«  February 2012  »
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
2728291234
567891011
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog