Add the column Description property to the table designer view in SQL Server Management Studio 2012

Wouldn't it be nice to see the column Description property right beside the Allow Nulls column for the columns in your table? This seems like a no-brainer, but it is not that easy to do in SQL Server Management Studio (SSMS). Unfortunately, you have to edit the registry. But if you do it correctly (be careful), you can take your table designer view from looking like this:

SQL Server Management Studio before Description property is added...

to this:

SQL Server Management Studio after Description property is added...

This looks so much more useful!

Follow these instructions for how to do this for SQL Server Management Studio 2012. See the bottom for a link that should work for older versions of SSMS.

USE CAUTION BELOW.

REGEDIT, if not used correctly, can really mess up your computer. I recommend backing up your registry BEFORE trying this. Be sure to back up the WHOLE registry, and not just a portion of it when using File>Export. Notice that at the bottom of the save dialog you can choose the Export Range. You should choose All, so you save the whole registry and not just a portion of it…

Using regedit, modify your registry by going to the following location (for SQL Server Management Studio 2012)

HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\DataProject

Find and modify the following keys:
• SSVPropViewColumnsSQL70
• SSVPropViewColumnsSQL80

Modify the values of these keys from    1,2,6;    to    1,2,6,17;    which is effectively just adding 17, which is the value for the table column Description property. You could add probably add other properties as well, as noted below, but I haven’t tried it…

Once you have made the changes, exit Regedit, and restart SQL Server Management Studio. Then you should see the Description column as shown above.

If you wanted to add other columns, you probably could... see the corresponding numbers below...

Property sequence:
1. Column Name
2. Data Type
3. Length
4. Precision
5. Scale
6. Allow Nulls
7. Default Value
8. Identity
9. Identity Seed
10. Identity Increment
11. Row GUID
12. Nullable
13. Condensed Type
14. Not for Replication
15. Formula
16. Collation
17. Description


For older versions of SQL Server Management Studio, see this link…

http://stackoverflow.com/questions/10537610/how-do-i-add-the-description-property-to-the-table-designer-view-in-ssms
Go there >

Find Count of Files in a Folder at the Command Prompt

So you want to find a count of the number of files in a directory using the command prompt or a batch file, etc. Here is a really easy way to do it.

Just run the dir command and pipe the results to the find command, using the count parameter:

dir|find /c ".tif"
  

This will return a count of all the TIF files in your folder. You could replace ".tif" with whatever extension you want, such as ".pdf"


What about a folder with multiple types of files in it? Just add the /b parameter to the dir command to get the bare filenames, and then just look for the single period in the find command, like so:

dir /b|find /c "."


An alternate version that would count all the files in the directory and any sub directories (by adding the /s parameter to the dir command) would be:

dir /b /s|find /c "."


The above won't work if the filenames have other periods in them, like some.name.txt, but for the average filename, this should work just fine.


Lastly, here is the approach that will work with filenames, like some.name.txt, that have multiple periods in them, because it counts the lines, not the periods, in the output:

dir /b /a-d | find /c /v ""


That last one came from Joey:

Go Visit Joey's explanation...



Hope this helps someone out there...


Keywords: batch file, batch script counting files in a folder

SQL Server 2005 Restore Database Error: The operating system returned the error '5(Access is denied.)'

I was trying to restore some old SQL Server 2005 databases to get some data they contained, when I ran into this error:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\test.mdf'. (Microsoft.SqlServer.Smo)

Your error message may look similar, except for the file location on the C drive, or whatever drive you are using.

After some frustration, I figured out the problem. I was actually trying to write my .mdf and .ldf files in a folder where they should not be located. That is why "Access is Denied" and it should be! You should write the files where your other database files are written. (If you need to find where they are, in SQL 2005, set up your Restore Database popup to restore from a currently active database, then go to the Options page on the left, and under "Restore the database files as:" look at "Restore As" which will contain the correct file location.)

In my error case, this location was not where my currently active databases where located, but was set to:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Your settings may be similar. In my case, this was definitely the wrong location, in fact, the wrong drive!

So to fix my situation, (and hopefully yours), I had to choose a new path and location for my .mdf and .ldf files. This is where the newly restored database files will be stored. (Which should be in the same folder as your other active databases):

1. Starting from the Restore Database popup, look to the left and choose the Options page (in SQL 2005), then look for "Restore the database files as" right in the middle of the page.
2. Just look to the right of the Restore As cell (on the Restore Database Options page), and click on the edit box symbol, which will bring up the Locate Database Files popup. Choose the correct path to the directory where you want the .mdf file to be stored, then enter the correct filename. Do the same for the .ldf file. 

Once I did this for both files, I was able to restore the database successfully. My assumption is that this will be similar for issues with SQL 2008 and 2012, etc.

Hope this helps you...

I was having trouble on Windows 2008 R2 running a batch script from a (local) shared drive as an Administrator (by right clicking and choosing run as Administrator). The command prompt window would just flash and never run successfully run the script, so doing the following made the shared drive "visible" to the Administrator profile, and would then allow successful running of the script.

Here is what I did, using ideas gleaned from the websites listed below... and copying some of their comments... Thanks guys...


--I wanted to create a service that would allow me to spawn a command prompt under the context of the local system

sc create syscommand binpath= "cmd /K start" type= own type= interact


--Then I started the service

sc start syscommand


--Voila, apart from a few minor errors, the command prompt popped up (sigh of relief)

--Run Net to map a drive for the system context, then every user account, including the Administrator, will have access to that shared drive

NET USE Z: \\MyLocalMachine\MyLocalShare


--removed the service account

sc delete syscommand



Ideas taken from:


http://wmug.co.uk/wmug/b/scambler/archive/2011/02/07/interactive-command-prompt-under-the-system-context-and-server-2008.aspx

and

http://www.sevenforums.com/network-sharing/34110-run-admin-network-drive.html

Convert Excel .xls to .xlsx and .xlsm using VBA

Here are some Excel macros to quickly convert .xls files to .xlsx and .xlsm formats for newer versions of Microsoft Excel. Just paste this in your VBA window (in a module) in a .xlsm file, and use Excel to automatically do its own conversions. You then run either macro, which will ask you to choose a folder, and then all .xls files will be converted to .xlsx or .xlsm depending on whether they have macros or not...


Sub Copy_XLS_as_XLSX()

    Convert_XLS_to_XLSX False
 
End Sub

Sub Delete_XLS_after_Copy_XLS_as_XLSX()

    Convert_XLS_to_XLSX True
 
End Sub

Sub Convert_XLS_to_XLSX(ByVal deleteXLS As Boolean)
 
    ' Allow user to choose a folder,  where all .xls files in that folder will be converted to
    ' .xlsx or .xlsm format, depending on whether they have macros or not...
 
     
 
    Dim xDirect$, xFname$, InitialFoldr$
    Dim wbk As New Workbook
    Dim msg As Integer
 
 
 
    InitialFoldr$ = "c:\temp\"    'Startup folder to begin searching from
 
    If deleteXLS = True Then  'as user if they really want to delete .xls files
                 
        msg = MsgBox("Do you want to delete all .xls files after you have created a copy in .xlsx format? If you are not sure, click NO!", vbYesNo, "Ready to delete .xls files?")
 
    End If
 
    If msg = vbNo Then  'user doesn't want to delete files...
 
        deleteXLS = False
     
    End If
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder containing the .xls files you want to convert..."
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
         
            Do While xFname$ <> ""  'loop through all filenames in folder
         
                If Right(xFname$, 4) = ".xls" Then  'only convert .xls files
             
                    Application.DisplayAlerts = False  'turn off any unwanted messages
                 
                    Set wbk = Workbooks.Open(Filename:=xDirect$ & xFname$)
         
                    If wbk.HasVBProject Then  ' convert Excel files containing Macros
                      wbk.SaveAs Filename:=xDirect$ & xFname$ & "m", _
                        FileFormat:=xlOpenXMLWorkbookMacroEnabled
                     
                    Else  ' convert standard Excel files
                       wbk.SaveAs Filename:=xDirect$ & xFname$ & "x", _
                        FileFormat:=xlOpenXMLWorkbook
                    End If
                 
                    wbk.Close SaveChanges:=False
                 
                    If deleteXLS = True Then  'delete existing xls files if desired
                 
                        With New FileSystemObject 'include Excel reference to Microsoft Scripting.Runtime library... or this won't work...  Go to Tools>References in the VBA editing window
                     
                            If .FileExists(xDirect$ & xFname$) Then
                                .DeleteFile xDirect$ & xFname$
                            End If
                         
                        End With
                     
                    End If
                 
                    Application.DisplayAlerts = True  'turn messages back on
                 
                End If
             
                xFname$ = Dir  ' get next filename in folder
             
            Loop
         
        End If
     
    End With
 
    xRow = MsgBox("All .xls files have now been converted.", , "Finished!")
 
 
 
End Sub








PDF Bookmarks: Change Zoom level and Name of PDF Bookmarks

If you are wanting to change the Bookmark zoom level on a bunch of your PDF bookmarks in Adobe Acrobat, or just modify the name of your bookmarks, here is the easy way to do it:


Use JPDFBookmarks:

http://sourceforge.net/projects/jpdfbookmarks/



And follow these instructions:

1. Modify one of your bookmarks to inherit the zoom (or choose whatever type of zoom you want)
2. Download JPDFBookmarks
3. Open the zip file and extract the files, then open JPDFBookmarks.
4. Open your PDF file in JPDFBookmarks.
5. Dump the bookmark data to a text file using JPDFBookmarks with the command Tools>Dump.
6. Find the bookmark you edited (in the text file that opens,) copy the correct portion of the text associated with your edited bookmark that is different from all the other bookmarks, such as TopLeftZoom,0,0,0.0, and replace the other setting on all the other bookmarks, such as FitWidth, -6,  using Find and Replace in Notepad.
7. Change the name of any bookmark as well, by editing the name of the bookmark.
8. Save the changes to the text file.
9. In JPDFBookmarks, load the bookmark data back using Tools>Load
10. Save the file in JPDFBookmarks. 
11. Open the PDF in Adobe Acrobat, and the bookmarks are fixed.

This is a great open source solution for working with PDF bookmarks, and changing the zoom level of existing PDF bookmarks.

See also:

http://www.serpentus.com/2011/05/10/acrobat-set-all-bookmarks-to-inherit-zoom/

Keywords:

Rename many PDF Bookmarks
Inherit Zoom
Fit Page
Actual Size
Fit Width
Fit Visible
Named Destination
pdf bookmarks inherit zoom
dozens hundreds thousands many bunch
edit PDF bookmarks

Get data from a SQL Server Stored Procedure into Excel automatically

Intro:

Do you want to get data from a stored procedure in SQL Server and have it drop right into MS Excel automatically? I recently needed just that, so follow this simple tutorial to get the job done right away...

How to run a SQL Stored Procedure from Excel without using VBA, and even include dynamic parameters that can be stored in cells in Microsoft Excel!!!



To pull data from a SQL Server Stored Procedure in Excel using parameters, do the following:


Excel 2007 Instructions:



1.  Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down.  Then Choose "From Microsoft Query"

2.  Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.

3.  Close the "Add Tables" popup if necessary.

4.  Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.

5.  Enter the following syntax:  {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}

    For example:  {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}

    Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.

6.  Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.

7.  You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.

8.  Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.

9.  You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.

10.  Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.

11.  Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.

12.  If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"

13.  Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.

14.  You should now have some data straight from your stored procedure.



Hope this helps someone out there!










Here are some common error messages that you may experience when trying to do this...

     No value given for one or more required parameters.

     Parameters are not allowed in queries that can't be displayed graphically.

     Could not find stored procedure ...    







Tags and Common Search Phrases:



connect excel to stored procedure

Excel Get external data from stored procedure
Microsoft query to SQL Server stored procedure from excel
get data into excel from stored procedure
add parameters to excel for stored procedure