March 2013 Entries

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 */


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;

The immediate workaround to this problem is to open up a new SSMS session, then navigate to the SQL Agent Job you want to edit. At that point, you will be able to make the edit and save the changes.

For more information on why this error occurs and the possible release(s) fixes, check out this url:

http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps
If you are reading a flat file, you are missing a delimiter somewhere and SSIS is trying to parse for the next value--so it thows an i/o buffer error. Look in the file for the missing delimiter. If the file is too big for that, try chopping up the file up into smaller versions until you find the line with the missing delimiter.
1. Navigate to the project. Right-click, go to the Properties menu.
2. Go to the Java Build Path. Choose Libraries. Click the Add External JARs button.
3. Select the jar file/library to add.

NOTE: For projects you own, that you would like to add, use the Add JARs button instead.

To edit the text in a title or border in Visio 2010:

1. Look at the bottom of the document, click on the tab for background page  (usually called: VBackground-1)

2. Navigate the shape that has the text you want to change; type in the new text.

For a quick changeover, go to:

Home -> Database -> Display Options ->Relationship

Select the relationship, then Database Properties -> Miscellaneous.

right click the results or your "Edit Top 999 Rows" query in SSMS. There's an option called: "Pane -> SQL" If you look at it, this show you the sql--you can edit that and narrow down the rows you are interested in editing even further.