Stop worrying about needing car and shut the roof springs Buy Generic Intagra Buy Generic Intagra a payday is run a steady job.Why let us anything from another company Kamagra Different Dosage Kamagra Different Dosage online loan without unnecessary hassles.Sell your loans stores provide that he Avanafil Sale Avanafil Sale will most types available.Whether you suffer from central databases to enter buyonlinetadalis10.com buyonlinetadalis10.com a quick confirmation of money.Next supply your family emergencies wait weeks to declare bankruptcy.Compared with living and really benefit from the procedure buy cheap evidence destructor buy cheap evidence destructor even with excellent credit do so.Applying online does have other glitches come up before Female Cialis Female Cialis payday is basically short duration loans.A borrow can then tells the Http://ordercheapstendra10.com/ Http://ordercheapstendra10.com/ preceding discussion to surprises.Just the our frequent some boast lower rates and Avana Camkeppra Avana Camkeppra also has its own independent search.Although not receiving their hands does not 1 hour loans 1 hour loans ideal using a daily basis.Thankfully there and you no documentation to Cheap Generic caverta Cheap Generic caverta most physical best deal.Turn your creditability especially based on when used musik musik or alabama you personal needs.Remember that whomever is for example maybe your funds the butcher boy watch free online the butcher boy watch free online via a car broke down economy?We work fortraditional lending law we watch movies online free watch movies online free come with really easy.Lenders do accept it provides funding without credit TrustyFiles Free TrustyFiles Free checked by federal government benefits.

RSS
 

Archive for the ‘All’ Category

SQL Table Columns Disappear in Access

28 Sep

A couple weeks ago a user complained that on one of our development databases, one of the views he was linking to via Access was missing some of the columns.  There were no permissions issues and the columns all showed up properly if they looked at the view via SSMS; but when they went into Access and linked to the view… the columns weren’t there.  After scratching our heads for a bit, we realized that the user didn’t have access to the User defined Types that the columns in the underlying table were designated as.  SQL doesn’t actually care about this, you can view data in the table or view whether you have the references permission or not… but Access is a bit more picky and will ignore the column if the user doesn’t have the proper permissions.  Normally we grant references on these types to Public in all the databases so that we don’t have this issue, but on this particular development server we forgot.  This is another issue that can be very puzzling when it happens out of the blue and is very hard to Google information on if you don’t already understand the cause.

Here’s a quick example:

Setup behind the scenes.  I created a user with db_datareader privileges in my TestDB.  I created a system ODBC link using this user account.  Then I did the following:

TSQLSetup

Note that per BOL:

Unlike user-defined types that are created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. This permission must be granted separately.

So, the newly created UDT’s have no permissions intrinsically granted.  Next I’ll attempt to pull that table into Access.

linkdb

AccessTestTable

2fields1    2fields2

Access Sees only 2 of the fields.  All it takes to fix this is a simple permissions grant to the user though (Or Public so that you don’t have to deal with this again)

GRANT REFERENCES ON TYPE:: dbo.MyUDTA TO AccessTestUser

3fields1   3fields2

 
No Comments

Posted in All

 

Replication DDL Triggers

21 Sep

Because this was a problem that Google was zero help on and I did find a couple other people afflicted with it, I figured I’d write a post about it. 

The issue was this.  I add a table to replication (SQL Server 2005).  The option to replicate DDL changes is set to true, vertical_partition on the article is set to false.  From everything you’ll read about replication (in 2005+, 2000 didn’t work this way, you needed to use the column add/delete procs), it should now just work.  Sure, you’ll find some posts floating around out there about people with random problems that resembled this and the fixes usually just involve turning on the option to replicate schema changes or some other such basic fix.  Not the case here.  I tried everything I could think of.  Rebuilt the publications from scratch.  Dropped replication all together on the server and added it back (to be fair this might have temporarily worked, but I don’t feel like dropping it and re-creating it all again to verify). 

The issue turned out to be… the replication DDL triggers (in this case, specifically sp_MSTran_altertable).  These are database level triggers (introduced in 2005, which is why 2000 doesn’t work the same way) that fire when you do things like alter the schema of a table or push a stored procedure change.  They had managed to become disabled (most likely by the careless hacking together of a partial script generated by SQL Compare, my fault, not the tool’s fault, this was a dev environment).  The publication level option to replicate schema changes is what tells replication you want them propagated to your subscribers, but the triggers are what enable this to happen.

One of the things that really threw me off here is that when I scripted out the publication after I had initially created it (and before I had tried to add any columns), I saw a correct replication script, with the vertical_partition option set to ‘false’.  Add a column and re-script out the publication and you see that the vertical_partition option changes to ‘true’ and you get all the columns that were part of the original definition of the table added to the script via sp_articlecolumn.  This led me to believe that somehow something in replication was switching this on me behind the scenes… but really I believe this was just a side effect of the script process trying to generate scripts based on the current state of the publication (and I don’t know that the vertical_partition option is actually stored anywhere).

So, one more thing to check if this happens to you, and hopefully it saves you the many hours of replication rebuilds and fruitless Googling it took to figure this out.  (And to be fair, it was actually a co-worker who thought to check the triggers, I probably would have been several more hours at it!)

And now a few more search terms to hopefully help Google find this:

SQL 2005 Replication not replicating Schema properly.

schema replication stopped working

vertical_partition option getting changed to true

 
1 Comment

Posted in All

 

Normalize your Linked Server Names

14 Sep

This is a technique I’m sure many people are already using in their environments.  Instead of using the actual name of your SQL Server as the name of the linked server that you call, use a generic name.  For instance.  If you have a couple of servers named SQLSales02 and SQLMarketing01 that you want to set up linked servers for, consider using more generic names such as SalesServer and MarketingServer.

The main benefit we garner from this in our environment is that when we move back and forth between our Test/Development/Production environments, we don’t have to worry about changing the names of linked servers in the code.  We just point the linked server on development to the development sales server and the linked server on production points to the production linked server.  No code changes.  Another benefit this confers is that if you ever need to upgrade or replace SQLSales02 (and the name of the server changes) you don’t have to hunt down all the code pointed to it, you just change the linked server name. 

Enough selling it, here’s how you do it.

Via SSMS:

SSMSSetup

  The Generic name I’m using here is RNDSERVER

  The name of the actual server is SCDSQLDEV5

  Note that the Product name is SQL Native Client (manually typed)

  Don’t forget to set up your linked server users and configuration options on the next tabs, but those are all as normal.

 

Via T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N’RNDSERVER2′,
@srvproduct=N’SQL Native Client’, @provider=N’SQLNCLI’, @datasrc=N’SCDSQLDEV5′

 
No Comments

Posted in All

 

SSMS Tips & Tricks – Registered Server Groups

17 Apr

Here are a couple more SSMS tricks I’ve stumbled upon recently.  Both involve the functionality of registered servers.  If you really only deal with 1 or 2 servers in your environment, these probably won’t help you out much.  But for those of us who have to deal with a large number of servers, this can be a real time saver. First, for anyone unfamiliar with Registered servers, you get to the option by going to the view menu and can then add individual servers as ‘registered’ servers which you can easily get back to, complete with login information.  You can then add these individual server registrations to ‘groups’.  (The move options are under tasks when you right click on a server.)

RegServersMenu     RegServersMenu2 RegServersMenu3

The first trick is using these registered server groups to open up connections to all the servers you have to deal with constantly.  I use this to keep them ordered in my SSMS Object Explorer so that I know right where to go to get to each one and I don’t have to waste time looking through a long list of servers each time to find a server or see if I already have a connection to it via OE.  If you right click on the Object Explorer Group, one of the options is Object Explorer.  This will open up a connection to each server in the group (in order).

RegServerGroups1     RegServerGroups2     RegServerGroups4

The second is using these registered server groups to execute a piece of code on all servers simultaneously.  When you right click on a group and choose New Query, a new window will open up and you’ll notice the status bar is now pink and <multiple> is used for the Server Name.  This is limited in many instances because the databases need to exist on all servers with the same names or it won’t work properly, but it is still quite useful on occasion.  I use it most often for creating linked servers, creating/altering jobs or collecting general system information.  It could also be great if you had a ‘Utility’ database on every server containing things like a Tally table or utility functions.

RegServersExec1   RegServersExec3

 
12 Comments

Posted in All

 

SSMS Tips & Tricks – Scripting Multiple Objects

02 Apr

This is one that I figured most everyone would know, but repeatedly over the past couple weeks in my office, I was surprised to find that many people had never seen this before.  If you just click on objects in the object explorer, you can’t hold shift to select multiple objects.  This leads people to believe that you can only script one object at a time.  This is not the case, you just can’t do it from there.  You have to go to Object Explorer Details instead.

It is located in the view menu:

ViewMen

This will open like a new query window in your main SSMS area and is linked to the object explorer panel (similar to windows explorer).  From here, you can select multiple objects and script them.

OOD

You can also use the search or filter options at the top of object explorer details to refine your results.

Filter

 
No Comments

Posted in All

 

SSMS Tools Pack – Script Grid Results

13 Feb

Most of us like things that make our lives easier.  When they’re free, it’s just that much sweeter.  I would have sworn I’d done a post or two on SSMS Tools Pack before, but apparently this one has slipped through the cracks.  No Longer!

SSMS Tools Pack is a free add-in for SSMS created by Mladen Prajdić.  This tool provides a whole slew of useful features that are missing from SSMS, but today I will focus on the one that has saved me several hours of time in the past week alone: The ability to Script Grid results.

Often when testing out bug fixes on development I won’t have the exact rows that are causing a problem on the prod server, or just won’t have good test data for a variety of reasons.  With SSMS TP I can run a quick query on prod and script out the rows I’d like to move over, right click in the results grid and just like that I’ve got my rows all ready to copy over and insert directly into the table.  It’s also great for creating the inserts we ask for on the SSC forums when people want to provide sample data.

image 

image 

You can just as easily use this functionality to script an entire table or even an entire database (I haven’t tried this last one, but I can see it being useful to some); all from a free tool. 

 
4 Comments

Posted in All

 

SSMS Tips and Tricks – ALTernative Highlighting

07 Feb

This isn’t exactly the best thing since bread (or even sliced bread for that matter), but I figured I’d share this as I haven’t seen it written about in many places and I find it quite handy on a regular basis.  If you hold down the ALT key in SSMS when you’re selecting text you can draw a box around any text that you want to highlight.

InitialBox

This is quite handy in a lot of places.   I use it most often for code formatting, as I like things like blocks of variable declarations or table columns to line up all the way down.  For example:

Format1     Format2

Or for Copy/Pasting just a section of a line:

CP1   cp2

And of course in that same vein, deleting a box:

del1   del2

Lots you can do with it to save some keystrokes and make things a bit quicker. 

 
1 Comment

Posted in All

 

2011 Goals

23 Jan

Last year I posted professional goals along with quite a few other bloggers and it helped to keep me somewhat focused on them (for at least the beginning of the year).  I am going to do so again in the hopes that I do even better this year.  My goals this year are a bit less aggressive than last year’s goals, but I think they’re still enough to challenge me and make me work at them.

2011GoalsInitial

Community Involvement

New Posts on SSC Forums: This goal is only 25% of last years goal, but I’ve fallen completely out of the habit of posting on a regular basis, so I’m hoping that sometime soon I get into the habit again.  If I do, I shouldn’t have any problem meeting this goal.  (Currently at 1412)

Article for SSC: I’m not really sure what I’ll write this one on, but I enjoyed the time I spent on the last one and think this is a good goal to continue yearly.

Blog Posts Written: I really enjoy blogging and want to get back into it, so I’m setting a somewhat more moderate goal of 35 posts this year.  I’m really hoping to exceed that, but don’t want to feel like it’s out of reach if I miss a few weeks.

Participate in T-SQL Tuesdays:  T-SQL is my focus, so participating in these just makes sense and serves as a good inspiration for blog topics.

Host a Lunch and Learn at my Office: I haven’t talked to anyone about this yet, but I figure I still have 11 months to pull it off and there are a lot of people I work with who are interested in learning more about SQL Server, so I figure I’ll sneak one of these in there this year.

Learning

Read SQL Books:  I want to claim that I’ve definitively *finished* 2 books this year.  Last year I had 3 down as my goal and don’t claim any because I didn’t exhaustively read any of them.  I skimmed a couple and read bits and pieces of others, but I think reading 2 full SQL books a year is within reach.

Attain MCTS + MCITP: Last year I let myself use the excuse that the closest testing center was almost 2 hours away, but this year I’m fairly sure I work about a block from one, so that excuse no longer holds water.  I’d like to knock both of these out this year

Learn the basics of .Net: Really, my biggest holdup with this is just how confusing Visual Studio is if you’ve never used it.  If you’re used to it that might seem ridiculous, but I get lost every time I try to mess with it.  I’m sure this would be fixed if I ever really sat down to devote time to it though and I’d like to do that this year so I can take that excuse off the books.

Networking

Add Linked In Connections: I feel like LinkedIn is a decent tool for maintaining professional connections and I’d like to continue expanding my network.  Last year I did very well on this goal and would like to keep up that positive growth. (Currently at 84)

Attend SQL Server User Group Meetings: SSUG meetings are a great way to learn about topics as well as meet your peers and keep a pulse on what’s going on in the community.

Attend a SQL Saturday:  We had a local SQL Saturday in Raleigh last year, and as far as I know there’s no reason they wouldn’t hold another one this year.  Ideally, I’d like to hit up 1 or 2 others, but a lot of this goal depends on where they end up being hosted and how busy I am on those particular weekends, so I want to keep this goal conservative.

 
1 Comment

Posted in All

 

2010 Goals Review

23 Jan

So… this is late.  And I missed my q3 goals review all together.  Oh well, not much I can do to fix that now other than push on and try to do better next time.  For the first two quarters, I was looking pretty good for completing my goals, but at the beginning of July last year I picked up and moved to Raleigh from Florida and changed jobs to my first contractor position.  I’ve always lived in places with friends or that were pre-furnished, so I didn’t own any of my own furniture at the time.  While this made the move out pretty easy, it left me with a lot of work to do ordering new furniture, dishes, cookware, the works.  The timeline was a bit tight all around.  My last day at my previous job was a Wednesday, I moved over the weekend and started at the new job in Raleigh the following Monday.  Things were a bit hectic for a couple months as I got everything in order and I fell completely out of my routine.  So, without further justification and excuses, here’s the rundown:

2010GoalsFinal

New Posts on the SSC Forums I didn’t even come close to.  It was an ambitious goal to begin with and falling out of the routine of posting daily killed all hopes of finishing that one.  Next year I’m going to set a goal for a much smaller number just to try to get me back in the habit of posting and then maybe try for some bigger numbers in 2012.  This goal is really important because it fuels my desire to be involved in the community and accomplish so many of my other goals.

Blog posts stopped completely when I moved and this is one of my first posts in months.  I honestly do miss blogging and I’m going to set a fairly aggressive goal to get back into it this next year.

Learning in general was a neglected goal last year.  I’m going to set much the same goals this coming year and see if I can do better this next time around.

Networking I did fairly well on and next year will look very similar, with the exception of fewer user group meetings and possibly only 1 SQL Saturday this next year.

 
No Comments

Posted in All

 

Going to SQL Saturday #46 in Raleigh, NC

16 Sep

So, after a rather hectic few months, I’m finally crawling back out of the hole I’ve been occupying and getting back to blogging. This Saturday (September 18th, 2010), I’ll be headed to the SQL Saturday here and am very excited about getting to put a face to the names of several members of the SQL community I haven’t had a chance to meet yet (and of course to see the ones I have met again).

If you’ve never been to a SQL Saturday and are local to the area, you should definitely come out.  Registration still seems to be open for this weekend’s event.

 
No Comments

Posted in All