AmosFiveSix.com

Experience, Knowledge, Creativity

  • Increase font size
  • Default font size
  • Decrease font size
Home Blog
Blog

SQL Server Management Studio Tricks

E-mail Print PDF

This post was inspired by Quick poll : what is your favorite Management Studio tip or trick? by Aaron Bertrand on SQLblog.com.

My favorite SMSS (2005 for me) tricks include:

  • CTRL-Tab for switching between windows.
  • CTRL-R for showing and hiding the results pane.
  • Include column headers when copying or saving the results options under Tools | Options | Query Results | SQL Server | Results to Grid.
  • Highlight any table or view name in T-SQL and hit Alt+F1 for sp_help and a list of its fields. (Suggested by dan from the above mentioned post.)

More tips here: DAT315 – Session Review for SSMS Tips & Tricks Session by Bill Ramos.

 

More SQL Server 64-Bit Issues

E-mail Print PDF

Here are some more issued we encountered when moving up to a 64-Bit SQL Server 2005. 

  •  64-Bit Product level is insufficient errors - This happened to us because we didn't have full SSIS installed on the server, just the development version.
  • 64-Bit Offline Mode is True error - This just required a regsvr on a DLL.
  • 64-Bit Linked Server Error - 64-bit SQL Server calls a stored procedure that doesn't exist on linked SQL 2000 servers. Creating a wrapper around a similar existing procedure fixed the problem for us.
  •  

    64 Bit SQL Server SSIS Packages with Excel and Access Data

    E-mail Print PDF

    If you've just switched to the 64-bit version of SQL Server and have SSIS packages that access Excel or Access, you may find that you packages stop working. That's because the JET driver that is used to access Excel and Access is 32-bit only. SSIS normally uses the 64-bit DTExec to run SSIS packages, it has not access to the 32-bit drivers. Here are some articles with all the details and work arounds

    Quick Reference: SSIS in 32- and 64-bits
    http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html

    SSIS Snack: SSIS, Excel, and a 64-bit OS
    http://sqlblog.com/blogs/andy_leonard/archive/2010/01/19/ssis-snack-ssis-excel-and-a-64-bit-os.aspx 

    64-bit Excel Driver in Microsoft Office 14
    http://dougbert.com/blogs/dougbert/archive/2009/08/28/64-bit-excel-driver-in-microsoft-office-14.aspx

    Excel (and Access) Data Source in 64-bit environment
    http://ssis.trigonblue.com/Excelin64bitenv/tabid/523/Default.aspx


    SSIS - An OLEDB Error Has Occurred
    http://blogs.msdn.com/debarchan/archive/2009/04/13/ssis-an-oledb-error-has-occurred.aspx


    MSDN: Integration Services Considerations on 64-bit Computers
    http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx


    MSKB: How to schedule the 64-bit SQL Server Agent to run a package by using the 32-bit version of the DTExec.exe utility
    http://support.microsoft.com/kb/934653


    Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered"
    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b1d5de28-b80b-4f34-a135-c68e87039c58/


    64 Bit SQL Server and Scheduling SSIS Packages
    http://sqlblogcasts.com/blogs/lawrenso/archive/2009/06/18/64-bit-sql-server-and-scheduling-ssis-packages.aspx|


    MSJET 4.0 (Excel, Access) in 64 bit environment
    http://blogs.msdn.com/sqldev/archive/2008/10/22/msjet-4-0-in-64-bit-environment.aspx


    MS Connect: Need 64-bit JET provider for x64 box
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125117

    Running SSIS 32-bit drivers or tasks on a 64 bit machine

    MSKB: How to schedule the 64-bit SQL Server Agent to run a package by using the 32-bit version of the DTExec.exe utility

    PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found (For SQL Server 2000 but it has some nice tips anyway.)

    Notes for running Integration Services on 64-bit hardware, x64 and IA64.

     

    Facebook Groups vs. Pages

    E-mail Print PDF

    Facebook Groups

    • Has a set look for all groups
    • Can enable or disable discussion board, wall, photos, videos, links, related groups, related events
    • Cannot rearrange elements on the group's web page
    • Limited amount of basic info you can post at the top of the group's web page
    • Can easily message all group members
    • Can change your group type at any time
    • When a group administrator posts to the group, it appears as though that individual posted it
    • Group admins' names appear on the group's web page

    Facebook Pages

    • Starts out with a "wall" page that shows recent posts, like individual profiles
    • Supports multiple "tabs" (or "pages") that can have different content
    • Can include any applications you want (wall, photos, etc) on different tabs
    • Allows you to add blocks of artibrary HTML so you can show anything you want
    • Allows you to rearrange the applications on the page
    • No way to easily message all page fans
    • Cannot change the page type after it's created
    • When a group administrator posts to the group, it appears as though the page posted it
    • Page admins' names do not appear on the web page
    • Provides a Facebook "badge" you can put on your other websites that includes the logo, name and counts of updates and fans
    • Allows you to have a "username" for your page so you can have a shorter URL
     

    How to Check for the Continental United States

    E-mail Print PDF

    This information is based on the United States Postal Service standard abbreviations for states and territories: http://www.usps.com/ncsc/lookups/usps_abbreviations.html

    If your data needs to include addresses for the continental United States only, it's best to check if the address's state is included in the list of continental states, rather than checking if the state is not in the list of non-continental states.

    For example, this incorrect SQL snipit allows addresses in Guam and other territories to pass through:

    WHERE store.State NOT IN ('AK','HI','VI','PR')

    This is the correct code to check for continental United States:

    WHERE store.State IN ('AL', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY') AND store.Country = 'United States'
     

    Indexes for W3C HTML and CSS Docs

    E-mail Print PDF

    I just found these great indexes for the W3C HTML and CSS documentation. It uses a frameset with a list of all HTML elements or CSS properties in a frame on the left and the current W3C docs on the right. Very nice. I found them on Paul Parks’ website.

    HTML Index: http://www.parkscomputing.com/html4/

    CSS Index: http://meyerweb.com/eric/css/references/css2ref.html

     

    How to Create Alerts for SharePoint Groups

    E-mail Print PDF

    You can't normally create alerts for SharePoint groups. Here's how:

    1. E-mail enable the SharePoint group so that a distribution list/group is created in Active Directory.
    2. Edit the properties of the group to change its Group Type to Security. Only security groups will show up in SharePoint's address book. They will show up as "domain groups" in SharePoint.
    3. Give the domain group at least visitor permissions on the list that are you are creating the alerts for. Alerts won't be sent if the domain group doesn't have appropriate permissions. This will mean that you'll likely have both the SharePoint group and the domain group in the permissions list. Simply giving the SharePoint group permissions is not enough.
    4. Then setup the alert and use the address book to locate the domain group. In the address book you'll see it with the form "DOMAIN\group name".
    5. Now test out your alerts. If you have require approval turned on for the list, make sure you approve the items so alerts will be sent.
       
    • «
    •  Start 
    •  Prev 
    •  1 
    •  2 
    •  Next 
    •  End 
    • »


    Page 1 of 2

    My Twitter Feed


    Subscribe

    Subscribe by e-mail:

    Site Search

    Google Ads