Ask Paula!

...bringing you notes from the field...
posts - 117 , comments - 87 , trackbacks - 0

My Links

News

Copyright © 2008-2014 Paula DiTallo

Tag Cloud

Article Categories

Archives

Post Categories

Image Galleries

.NET Development

Enterprise Integration

Entertainment - Games

Java Development

Mobile/PDA Development

Professional Affiliations

Tuesday, July 15, 2014

How Do I Install an Eclipse Plugin from a *.zip File?

If you aren't able to install from the Help\Install New Software menu, it is probably because the zip file doesn't have the content.jar/artifacts.jar needed to do so. You will need to navigate to where you've installed Eclipse, then follow these steps:
 
1.  Navigate to the Dropin drectory
2. Create a new subdirectory (folder).
3. Expand the zip file to the subdirectory folder.
4. Restart Eclipse
5.  Under the Windows\Open Perspective menu, look for your plugin.

Posted On Tuesday, July 15, 2014 11:29 AM | Comments (1) | Filed Under [ Practical Answers Eclipse ]

Monday, June 30, 2014

Sql Server: Orphaned user name; Can't login, User name disappeared from my database

This usually happens when you restore a database. For example, you restore a copy of production database X to your QA server. In essence, you have overwritten the user info of that specific database with what exists in production. This creates an orphan user -- where you have no login associated with a user in that database on a server that once associated that user with the old copy of the database.

Here is what to do fix the issue:

1.  Validate that what you think is the problem is the  problem. Do this by listing the known orphans. You may have others show up--but be sure the id you are interested in is on the list:

use [your database instance]

go

EXEC sp_change_users_login 'Report'

2. Fix the broken login:

EXEC sp_change_users_login 'Auto_Fix', 'orphan username'


Posted On Monday, June 30, 2014 3:32 PM | Comments (0) | Filed Under [ Practical Answers ]

Thursday, June 26, 2014

SQL Server: Restoring a backup generates this error: Operating system error 5(Access is denied.).

There are several reasons for this. The most common reason is that the account you are using while logged into mssql through ssms does not have access to the drive definition you are using as your source. Network drives are a good example of an access conflict.

Assuming you have access to the drive, try redirecting the network drive to a local drive letter via xp_cmdshell  (Note: be sure use of xp_cmdshell is enabled) .

exec master..xp_cmdshell 'net use Z: "\\BackupServerXX\<share>\PathWithoutTrailingSlash" YourADpasswordHere /user:domain\your.Username'
Declare @restorefile varchar(1024) = 'Z:\thebackupfile.bak'
Restore database x
From disk = @restoreFile


When you are done, or if you've made a mistake and need to reassign the drive, delete the reference.

exec master..xp_cmdshell 'net use Z: /delete'

Posted On Thursday, June 26, 2014 3:32 PM | Comments (0) | Filed Under [ Practical Answers ]

Tuesday, May 20, 2014

SQL Server: How do I generate the table schema and populate it with inserts in a script?

In SSMS, there's a Generate Script utility (read:  only available under version 2008 and up) .

Here are the steps you would need to take to make use of the utility:

  1. Right click on the database you're interested in and go to Tasks -> Generate Scripts
  2. Select the tables and/or any other objects you'd like in order to get them into the script.
  3. Navigate to Set scripting options. Click on Advanced.
  4. Under the General category, navigate to Type of data to script
  5. Select the Schema and Data option to get the insert statements generated. Click OK.


Posted On Tuesday, May 20, 2014 5:46 PM | Comments (0) | Filed Under [ Practical Answers ]

Tuesday, May 6, 2014

How do I find the largest files on my disk (storage)?

Here's an example using forfiles to scan your storage. This command line will look for files over 200mb on the C:\ drive after 1/1/2014.

forfiles /P C:\ /M *.* /S /D +"01/01/2014"  /C "cmd /c if @fsize gtr 209715200 echo @path @fsize @fdate @ftime"

For more information, check out Microsoft's examples and syntax page:

http://technet.microsoft.com/en-us/library/cc753551.aspx

Posted On Tuesday, May 6, 2014 10:06 AM | Comments (0) | Filed Under [ Practical Answers ]

Monday, April 21, 2014

Sql Server: Select Count(*) throws the error: Arithmetic overflow error converting expression to data type int

Try using count_big(*) instead-- this is defined as bigint instead of int.

In this example, I am looking for how many 100s of millions of rows exist by product type where each row is roughly 300 bytes wide to ultimately obtain how many gigabytes of data I need to provision.

select x.ProductType,
       count_big(*) as ProdCount,
      (count_big(*)*300) as TotalBytes,
      ((count_big(*)*300)/1073741824) as Gigabytes
 from fdwintegration.etl.fuelpriceindex x
  inner join fdwintegration.etl.FuelProducts y
   on x.ProductIndicator = y.ProductIndicator
    and x.ProductType = y.ProductType
   where x.ProductIndicator = 'D'
   group by x.ProductType
order by x.ProductType

Posted On Monday, April 21, 2014 5:01 PM | Comments (1) | Filed Under [ Practical Answers ]

Tuesday, April 8, 2014

How Do I Alphabetize the Application Icons on my Droid?

Click on the App Button (aka: "Drawer" ) -- That's the key with the multiple squares, click on menu , select view type, select alphabetical grid.

Posted On Tuesday, April 8, 2014 12:48 AM | Comments (0) | Filed Under [ Practical Answers ]

Friday, December 13, 2013

Windows: How do I find what process is locking a file?

Open up an MSDOS command window. Type :

net file

The id, path, user name, loc# for the file will show up in a text list. Once you find the file you want unlock, you'll need to remember the id. To unlock the file, go back to the MSDOS command window. Type:

net file 99/close    (where 99 = file id)

On a windows server there are 2 other ways:
1. From an MSDOS command window type: Openfiles.exe /query /s YOUR_SERVER_NAME
2. Server Manager>Roles>File Services> Share and Storage Management (right click on SaSM) >Manage Open File



Posted On Friday, December 13, 2013 5:11 PM | Comments (0) | Filed Under [ Practical Answers ]

Wednesday, December 4, 2013

Linux/Unix: How do I find any partial file name on any directory?

We've all been there-- like -- where's that offline htm file i bothered to save last week ... oh I think it was blue something... or was it something blue?

find ../ -name '*blue*.htm*'  -print

If you want to keep it in an output file to sort through later while you do something else:

find ../ -name '*blue*.htm*'  -print > $HOME/ foundBlue.txt

 

Posted On Wednesday, December 4, 2013 12:24 PM | Comments (0) | Filed Under [ Practical Answers ]

Tuesday, October 8, 2013

My old (generation 2) nano iPOD won't shut off.


Try holding down the Menu and Selector (push-button circle in the center) down at the same time for at least 2 seconds.

Posted On Tuesday, October 8, 2013 8:01 PM | Comments (0) | Filed Under [ Practical Answers ]

My iPOD keeps saying "Do Not Disconnect" while charging. I can't listen to anything.

The simplest answer is because the iPOD software detects that it is unsafe (read: data loss) for your iPOD, Computer or both if you were to disconnect the cable without ejecting the device first.

Here's what to do:

On the Mac--

  1. Click on the Finder icon
  2. Pick the iPOD icon.
  3. Navigate to the File menu--select Eject iPOD

On a Windows OpSys device--

  1. On the Start menu, click Computer (e.g. Windows 7).
  2. Right-click on the drive/device letter or iPOD icon (read: the icon doesn't always appear)
  3. Select Eject.

The message should go away while you're still plugged in. You should be able to charge and listen at the same time! :)


Posted On Tuesday, October 8, 2013 7:58 PM | Comments (0) | Filed Under [ Practical Answers ]

Monday, October 7, 2013

How do I save frequently used SQL Statements in SQL Server?

One convenient way is to make use of the template feature (aka: Template Explorere) in SSMS.

1. Navigate to the View menu in SSMS
2. Select Template Explorer->right click on SQL Server Tempaltes.
3. Click New and create folder; use to add/edit templates.


Posted On Monday, October 7, 2013 12:27 PM | Comments (0) | Filed Under [ Practical Answers ]

How do I switch montor postions? Switching cables didn't help.

For Windows 7:
1. Right click any blank area on your desktop. 
2. Left click Personalize->Display Settings. A  window will open that shows a monitor.
3. Navigate to Change Display Settings. A new window will appear showing 2 monitors. 
4. Click Identify Monitors.  When the monitor you are on is the main monitor, you will see "This is currently your main display" under Multiple displays. To change this, click on the other monitor and check the box that says "Make this my main display". Click Apply.
5. If  you want to change how the mouse drags from right to left/left to right,  click and drag the right hand monitor left to the left of the original left monitor and release the drag.  That will change the positions of the monitors so that the mouse motion will be the way you want them.

Posted On Monday, October 7, 2013 9:54 AM | Comments (0) | Filed Under [ Practical Answers ]

Friday, September 27, 2013

Which DMVs are most helpful? Is there a list of them?

As with most questions asked about monitoring/assessing/administering MS SQL Server installations, the answer is "it depends".

There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (Dynamic Management Views) category, type this:

-- This will show you the views and the functions of all dmv objects
SELECT name, type, type_desc
FROM sys.system_objects
 WHERE name LIKE 'dm_%'
-- and type = 'V'
  ORDER BY name

Here are a few useful DMVs to get started with...

-- Query Plans
-- stats about cached query plans
select * from sys.dm_exec_query_stats

-- identifies query plans that are cached
select * from sys.dm_exec_cached_plans

-- I/O
select * from sys.dm_io_pending_io_requests
select * from sys.dm_io_virtual_file_stats(null,null)  -- //
stats for all data and log files

-- Reqs/Executions

-- lists active user/internal task connects
select * from sys.dm_exec_sessions

-- established connects
select * from sys.dm_exec_connections

-- lists info about all reqs executing
select * from sys.dm_exec_requests



Posted On Friday, September 27, 2013 7:23 PM | Comments (0) | Filed Under [ Practical Answers ]

Friday, September 20, 2013

How can I tell when a stored proc (or function) in sql server was last modified?

You're really looking of the modify_date in the sys.objects table where the type is either a stored proc or a function. This works on a database by database level -- to elevate this search to the server level, you'll have to use the loop sproc sp_msforeachdb
 

SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'  -- change 'P' to 'FN' if you're looking for a function 
 and modify_date between cast('2013-09-10' as date) and cast('2013-09-20' as date) 

Posted On Friday, September 20, 2013 5:22 PM | Comments (0) | Filed Under [ Practical Answers ]

Thursday, September 19, 2013

How do I change the value of a column in a CASE statement and eliminate NULLs?

In t-sql/mssql, if you have a known NULL value  you can issue a statement like:

SELECT
  ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate
FROM Drviers

The ISNULL function is replacing any nulls found in the BirthDate column to a default value of 1/1/1970.

In other situations, you may not have just NULL values, but other possibilities as well. 

SELECT
 ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate,
 CASE
   WHEN BusinessPhone IS NULL OR BusinessPhone = ''
        THEN 'Unknown'
   ELSE BusinessPhone
 END as BusinessPhone
FROM Drviers

The case statement is evaluating the value found for the BusinessPhone column. If the value is NULL or set to '', then the default value is Unknown, else if the value isn't null or set to '', then retain the value for the  BusinessPhone column found.

Posted On Thursday, September 19, 2013 9:57 AM | Comments (0) | Filed Under [ Practical Answers ]

Wednesday, September 18, 2013

I get the error "Can't connect to SSISServer" . How do I fix this?

If you're seeing this error, or a similar access denied error you'll need to have your windows account given DCOM permissions. DBAs generally have the ability to add you.

If you're a DBA and want to add a developer to be able to connect to the SSIS services to deploy packages here's a quick punch list:

  1. Logon to the target SQL Server. Go to Start->Run  type lusrmgr.msc
  2. In the Local Users and Groups dialog box, click Groups->Ditributed COM users
  3. Once in the Distributed COM users properties dialog box, click Add
  4. Select the User click OK
  5. Go back to Start->Run type dcomcnfg
  6. In the Component Services dialog box, drill down to Component Services->Computers->MyComputer
  7. Under DCOM Config, right click MsDtsServer, click Properties
  8. In MsDtsServer100 Properties dialog box, click Security tab
  9. Under Launch and Activate Permissions, go to Customize->Edit
  10. Under Launch Permissions click Add
  11. Under Select Users or Groups, type the user name, click OK
  12. Under Permissions for the user, Allow or check the checkbox for all 4 (local,remote launch/activation)
  13. Click OK. Do steps 11-12 for Access Permissions.

Posted On Wednesday, September 18, 2013 8:55 AM | Comments (0) | Filed Under [ Practical Answers ]

Wednesday, September 11, 2013

How do I do a SELECT TOP in SQL for DB2 on an iSeries (Power7) ?

This answer depends on what version of SQL you're running. A FETCH clause will work for all. Here's an example:

SELECT FLBILC,
       FLDATE,
       FLTCK#,
       FLCST#
  FROM PRDMETRO.FLHISTAF
 WHERE FLBILC = 'CMD'
 ORDER BY FLBILC
  FETCH FIRST 10 ROW ONLY;

The equivalent in T-SQL would be:

SELECT TOP 10 
       FLBILC,
       FLDATE,
       FLTCK#,
      FLCST#
 FROM PRDMETRO.FLHISTAF
  WHERE FLBILC = 'CMD';
    

Posted On Wednesday, September 11, 2013 5:19 PM | Comments (0) |

Wednesday, August 28, 2013

SQL Server Management Studio: Why do I get "Saving changes is not permitted" ?


This happens when you're in the SSMS gui making changes to the table that either affect the behavior of an index, cause existing data to be truncated/nulled, etc. This can also happen you try to change the order of columns, or change datatypes. 

If your credentials allow it, you can get past this message in order to truly shoot yourself in the foot! :)

  • Navigate to Tools --> Options
  • Expand Desingers
  • Click on  Table and Database Designers
  • Check the box Prevent Saving changes that require table to be re-created.



Posted On Wednesday, August 28, 2013 3:00 PM | Comments (0) |

Monday, August 26, 2013

Why can't I connect to SSIS through management studio?

The microsoft integration service is secure by default. The DBA has to grant access to the service in order for it to connect successfully through ssms.

To grant access to the Integration Services service

  1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.

  2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.

  3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.

  4. On the Security tab, click Edit in the Launch and Activation Permissions area.

  5. Add users and assign appropriate permissions, and then click Ok.

  6. Repeat steps 4 - 5 for Access Permissions.

  7. Restart SQL Server Management Studio.

  8. Restart the Integration Services Service. 

Posted On Monday, August 26, 2013 10:44 AM | Comments (1) |

Tuesday, August 20, 2013

How do I Install IBM Data Studio 4.1 ?

The download for this workbench is large--somewhere around 1.2 gb. Finding the install exe for linux or windows isn't clear. Basically this download appears to be from a 2 disk distributable set of *.ISO files. Ignore all the distraction scripts in the core directory. Delve into \ibm_ds410_lin64  (I downloaded the 64 bit version) . Look for a directory called Disk1. There you will find the installer images for Windows and Linux.

Here's a visual image of what the directory will look like.


Posted On Tuesday, August 20, 2013 12:04 PM | Comments (1) |

Thursday, July 11, 2013

SSIS: My Toolbox is Missing!

I work with BizTalk (BTS) and SSIS frequently. In one of my environments, whenever I've programmed in BTS, then jump back to SSIS, the SSIS toolbox is missing in action.

Here's what I do to solve the problem in that environment:

  • Save the application and close Visual Studio.
  • Go your AppData\Local\Microsoft\VisualStudio\[whatever version]
  • Rename the *.tdb files there to something else like *.tdb.x
  • Open Visual Studio  (Note: You will see the progress status bar re-initializing all available toolboxes.)
  • Open the application.
  • On the canvas, navigate to a space without an annotation or a component.
  • Right Click and select SSIS Toolbox.

Update: I recently upgraded from vs2010 to vs2012 in another environment without a biztalk installation-- in this case, when the toolbox wasn't visible, I just went to the canvas, did a right click, selected on SSIS Toolbox and that solved the issue. Try this action first. This may be all you need to do to resolve this issue in your environment.

Posted On Thursday, July 11, 2013 4:19 PM | Comments (2) |

Tuesday, July 2, 2013

How do I know what Version of BizTalk is on my server?

There are 2 ways to do this, the first is to query the BizTalkDBVersion table:

use [BizTalkMgmtDb]
go


SELECT DatabaseMajor, DatabaseMinor,ProductBuildNumber, ProductRevision
FROM dbo.BizTalkDBVersion; 


Here is a list of possible BizTalk versions (CUP = cumulative update package, SP = service pack) :

BTS2004
3.0.4902.0
BTS2004SP1
3.0.6070.0
BTS2004SP2
3.0.7405.0
BTS2006
3.5.1602.0
BTS2006R2
3.6.1404.0
BTS2009
3.8.368.0
BTS2010   
3.9.469.0
BTS2010CUP1
3.9.522.2
BTS2010CUP2
3.9.530.2
BTS2010CUP3
3.9.542.2
BTS2010CUP4
3.9.545.2
BTS2010CUP5
3.9.556.2
BTS2013   
3.10.229.0


The second way is to follow these steps:
  1. Click Start, click Run, type regedt32, and then click OK.
  2. Once the window is up, navigate to  HKEY_LOCAL_MACHINE,  then SOFTWARE, then Microsoft, then BizTalk Server, and finally open 3.0.
This is what you should see:





Posted On Tuesday, July 2, 2013 5:59 PM | Comments (0) |

Wednesday, March 20, 2013

SQL Server: I need to get to a remote server and management studio is broken. How can I login without ssms?

Try using osql .  It can be a life saver.  Here's an example of the most common usage:

osql -E -S myMSSQLServerName        /* This uses windows authentication to connect to a server */

osql -?                                                  /* lists all the switches available to use with osql */

Once you see the SQL prompt (1> ) , you can interact with the server.

1> USE myDatabaseName
2> GO
3> SELECT col1, col2
4> FROM myTable
5> GO
6>
7> EXIT   

Here's a link or two to get you started.
http://msdn.microsoft.com/en-us/library/aa214012(v=sql.80).aspx    /* options available */

http://msdn.microsoft.com/en-us/library/aa213088(v=sql.80).aspx    /* accessing data through osql */


Posted On Wednesday, March 20, 2013 8:06 PM | Comments (0) |

SQL Server: Is there a query I can use that generates similar output to Activity Monitor?

Yes!  This query will return very similar information to what activity monitor returns--including the text of the query the process is running (when applicable).

-- Query version of Activity Monitor

SELECT
   SessionId    = s.session_id,
   UserProcess  = CONVERT(CHAR(1), s.is_user_process),
   LoginInfo    = s.login_name,  
   DbInstance   = ISNULL(db_name(r.database_id), N''),
   TaskState    = ISNULL(t.task_state, N''),
   Command      = ISNULL(r.command, N''),
   App            = ISNULL(s.program_name, N''),
   WaitTime_ms  = ISNULL(w.wait_duration_ms, 0),
   WaitType     = ISNULL(w.wait_type, N''),
   WaitResource = ISNULL(w.resource_description, N''),
   BlockBy        = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   HeadBlocker  =
        CASE
            -- session has active request; is blocked; blocking others
            WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1'
            -- session idle; has an open tran; blocking others
            WHEN r.session_id IS NULL THEN '1'
            ELSE ''
        END,
   TotalCPU_ms        = s.cpu_time,
   TotalPhyIO_mb    = (s.reads + s.writes) * 8 / 1024,
   MemUsage_kb        = s.memory_usage * 8192 / 1024,
   OpenTrans        = ISNULL(r.open_transaction_count,0),
   LoginTime        = s.login_time,
   LastReqStartTime = s.last_request_start_time,
   HostName            = ISNULL(s.host_name, N''),
   NetworkAddr        = ISNULL(c.client_net_address, N''),
   ExecContext        = ISNULL(t.exec_context_id, 0),
   ReqId            = ISNULL(r.request_id, 0),
   WorkLoadGrp        = N'',
   LastCommandBatch = (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle))
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
    -- Using row_number to select longest wait for each thread,
    -- should be representative of other wait relationships if thread has multiple involvements.
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

WHERE s.session_Id > 50                         -- ignore anything pertaining to the system spids.

AND s.session_Id NOT IN (@@SPID)     -- let's avoid our own query! :)

ORDER BY s.session_id;

Posted On Wednesday, March 20, 2013 7:13 PM | Comments (1) |

Powered by: