SQL Server SSMS Saving changes is not permitted

You may receive this dialogue if you’re using a fresh install of SSMS, or after an upgrade. It simply means your environment hasn’t been configured to allow certain changes which require the dropping, and re-creation, of a table.

Such actions include:

  • Adding a new column anywhere other than the end of a table
  • Deleting a column
  • Updating column configuration, such as Allow Nulls or the Data Type
  • Changing the column order

You can modify your environmental settings to allow for these actions form the Tools menu. Click Options, expand Designers, and then click Table and Database Designers. Un-tick Prevent saving changes that require the table to be re-created and click OK.

You may now commit your table changes.

 

Advertisements

SQL – How to Update and Select in the same query

The OUTPUT clause Returns information on each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

These results can be returned to the processing application for further use.

The following example uses the OUTPUT clause to return row values in conjunction with an UPDATE query which effectively enables a SELECT and UPDATE command in the same query.
 
— CREATE TEMP TABLE
CREATE TABLE #TempTable ( URN INT, F1 VARCHAR( 50), F2 VARCHAR(50 ), USED VARCHAR (1))
— INSERT DATA
insert into #TempTable values ( 1,’F1_Val_1′ ,’F2_val_1′, ‘N’)
insert into #TempTable values ( 2,’F1_Val_2′ ,’F2_val_2′, ‘N’)
insert into #TempTable values ( 3,’F1_Val_3′ ,’F2_val_3′, ‘N’)

— UPDATE SINGLE ROW IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1 = ‘F1_New_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN = 1

— UPDATE MULTIPLE ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROWS
UPDATE #TempTable
SET F1 = ‘F1_And_F3_new_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (1 ,3)

— UPDATE ALL ROWS IN TABLE WITH NEW VALUE
— USING OUTPUT COMMAND TO RETURN FIELDS OF ALL ROWS IN TABLE
UPDATE #TempTable
SET F1 = ‘New_Global_Val’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2

— UPDATE TOP 1 ROW IN TABLE WITH NEW VALUE
— MARK USED = ‘Y’
— USING OUTPUT COMMAND TO RETURN FIELDS OF UPDATED ROW
UPDATE #TempTable
SET F1   = ‘F1_New_Val’
,USED = ‘Y’
OUTPUT Inserted.URN, Inserted.F1, Inserted.F2
WHERE URN in (select max( URN) from #TempTable WHERE used = ‘N’)

— DROP TEMP TABLE
DROP TABLE #TempTable

 

Java – How to scroll to a particular component in jScrollPane and gain focus

Pragmatically scrolling to a particular point in jScrollPane can be tricky, particularly if you have nested panels or layered panes. Here’s a piece of code I find myself using time and time again to have a jScrollPane scroll to a particular element and give it focus on screen.

1. Declare a JComponent and assign it the element requiring focus
JComponent comp = myTextField;
2. Adjust the vertical scroll bar to bring the component into view on screen. This is achieved by determining the Y location of the component in the scrollPane.
myScrollPane.getVerticalScrollBar().setValue(comp.getLocation().y-50);
I reduce the scroll integer value by 50 in my example to ensure the component is fully visible on screen.
3. The component is now visible on screen, so time to give focus.
comp.requestFocus();
Lets put it all together to scroll to, then give focus to, a particular component in a swing application contained in a jScrollPane.
JComponent comp = myTextField;
myScrollPane.getVerticalScrollBar().setValue(comp.getLocation().y-50);
comp.requestFocus();
If your swing application contains nested views in the scrollPane then you must call the getParent() method to move up the chain of views and establish the required scroll position.

Using the example above, lets say myTextField is contained in a jPanel, which itself is contained in the jScrollPane with an unknown Y position greater than 0.

The following code determines the Y position of the jPanel relative to the scrollPane as well as the Y position of myTextField relative to the jPanel. Sum the two values to set the scroll position.

myScrollPane.getVerticalScrollBar().setValue(comp.getParent().getLocation().y + (comp.getLocation().y – 50));
JComponent comp = myTextField;
myScrollPane.getVerticalScrollBar().setValue(comp.getParent().getLocation().y + (comp.getLocation().y – 50));
comp.requestFocus();
Next Steps…
You can manipulate the comp object’s visual properties to give the user prompts as to which field on the form has focus. I use this method to indicate a field that has failed form validation:
comp.setBackground(new java.awt.Color(255,204,204));

Mac – How to remove uninvited Google apps from launchpad

Screen Shot 2015-06-22 at 21.20.30

At the time of writing this, you get stuck with a bunch of Google apps added to your Mac’s Launchpad when you install Google Chrome.

These are:

  • Google Chrome (to be expected)
  • Google Drive
  • Gmail
  • YouTube and
  • Google Search

I found this to be annoyingly intrusive and a modification that I didn’t ask for nor agree to. I found it even more frustrating that you don’t simply navigate to your Applications folder to remove the uninvited guests.

Instead, you must navigate  to your users folder (Right-click on finder icon –> Go To Folder… –> enter forward slash [/] hit enter), enter your user account folder, find the Applications sub-folder and here we find a new folder named Chrome Apps.

Deleting the Chrome Apps folder removes the icons from Launchpad.

Register ActiveX DLL or OCX Files on 64-BIT Windows

regsvr32 c:\WINDOWS\system32\WebAgent4.ocx
Screenshot 2015-06-17 13.52.17

 

If you receive an error when registering a 32-bit DLL or OCX on a 64-bit version of Windows, follow these steps:

 

1. Open an elevated command prompt – its important the command window has Administrator access otherwise you will continue to see errors:

 

in Windows Vista or 7:
Select Start > All Programs > Accessories
Right-click on “Command Prompt” in the Accessories
Click “Run as Administrator”

 

in Windows 8:
Open the “Quick Link” menu (by holding down the Windows 8 logo key  and pressing the X key)
Select “Command Prompt (Admin)”
Select “Yes” to allow changes

 

2. If the 32-bit DLL or OCX is in the %systemroot%\System32 folder, move it to the %systemroot%\SysWoW64 folder.

 

3.Run the following command:
%systemroot%\SysWoW64\regsvr32 <full path of the DLL or OCX>


In my example this would be: %systemroot%\SysWOW64\regsvr32 C:\Windows\SysWOW64\WebAgent4.OCX
Screenshot 2015-06-17 13.56.25


^Source: https://support.microsoft.com/en-us/kb/249873

iOS / Objective-c – Simple NSTimer

Here’s a simple use of NSTimer to repeatedly trigger a method call every X seconds in Objective-c, for iOS.

Declare NSTimer variable in header file

NSTimer *myTimer;

Initialise myTimer at the appropriate point in code. Could be viewDidLoad on iPhone or willActivate on Apple Watch. My example triggers the method named updateDisplay at 5 second intervals. Note that I’ve set repeats: YES.
You can trigger the method for a one off call after X seconds by setting repeats: NO.

myTimer= [NSTimerscheduledTimerWithTimeInterval:5.0
target:self
selector:@selector(updateDisplay)
userInfo:nil
repeats:YES];

The above code both primes and starts the timer.

To end the timer. Could be didDeactivate on apple Watch, for example

[myTimer invalidate];
myTimer = nil;

How to take a screenshot on Apple Watch

It’s a little tricky but you can take a screenshot on Apple Watch, handy when you want a live shot of an app your developing. Or perhaps you want a lasting reminder of some notification or other. Either way, follow these steps:

1. Get a good grip of your Apple watch.

If you wear with the side buttons facing your hand:

Placing your thumb on the smooth side
Rest your first finger over the digital crown
Rest your middle finger on the side button

If you wear with the side buttons facing your elbow:

Placing your first finger on the smooth side
Rest your thumb over the digital crown and the side button

2. Press the digital crown and side button at the same time.

The two buttons pressed at the same time will result in a screenshot of the current display. A flash of the screen accompanied by the camera shutter sound signifies a successful screenshot.

3. View and share the image

The screenshot from your Apple watch is saved to the paired iPhones camera roll. Open photos on iPhone to view your screenshot. Tap the share button to view the options.

IMG_7954

P.S. you can see the full Apple Watch user guide here: apple.co/1IPuZoa

Java – JOptionPane with user input

A useful script to capture user input from JOptionPane:

String input = “”;

JTextField passwordField = new JTextField();

passwordField.setTransferHandler(null);

Object[] obj = {“Enter text”, passwordField};

Object stringArray[] = {“OK”, “Cancel”};

if (JOptionPane.showOptionDialog(null, obj, “Messdage with input”,
     JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE, null, stringArray, obj) == JOptionPane.YES_OPTION) {

input = passwordField.getText();

      // TODO – USE INPUT VALUE
      System.out.println(input);
}else{
     //DO NOTHING – cancel or cross clicked
}

 

JO1

jo2

Output:

Hello World!

SQL – View SQL Server Database Mail Log (All / Successful / Failed)

SQL Server keeps a log of mail sent via Database Mail and the sp_send_dbmail Stored Procedure.

I find these code snippets useful to view mail history including sent and failed items. You can view address, subject & body, file attachment(s), status, sent date, etc.

All Messages – regardless of status and deliverability
SELECT top 50 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC
Sent Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC
Failed Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

SQL Server – One-off Backup and restore of ALL SQL Server Agent jobs using SQL Server Management Studio

One-off Backup and restore of all SQL Server Agent jobs using SQL Server Management Studio

1. Connect to the SQL server using SQL Server Management Studio.

2. Navigate to the Object Explorer Details view by selecting View à Object Explorer Details from the menu bar or press F7.

3. Double-click SQL Server Agent from the Object Explorer Details window.

4. Double-click the Jobs folder to expand the list of agent jobs currently configured on the SQL Server.

5. At this point you have the option to select only the jobs you want to backup, or select all jobs on the server (Ctrl+A).

6. Once your selection has been made, right-click over the selected area, hover Script Job As à CREATE To à File …

7.Navigate to a file location and save your SQL Server Agent job(s) creation code to a .SQL file.

Open the SQL file to execute on another SQL Server to restore a previous version of agent job(s), or to create the job(s) at a new location.