Ask Paula!

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

Paula DiTallo — Thu, 21 Mar 2013 00:06:50 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/20/152475.aspx

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

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

Paula DiTallo — Wed, 20 Mar 2013 23:13:11 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/20/152474.aspx

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;

SQL Server Agent:: I Can't Edit a Job Step because of the error "Creating an instance of the COM component with CLSID from the IClassFactory failed"

Paula DiTallo — Mon, 18 Mar 2013 23:05:08 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/18/152450.aspx

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

SSIS: I get the error The column data for column "Column x" overflowed the disk I/O buffer

Paula DiTallo — Fri, 15 Mar 2013 21:34:34 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/15/152426.aspx

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.

Eclipse Project:: How do I add an external *.jar file to my project?

Paula DiTallo — Fri, 15 Mar 2013 01:15:50 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/14/152413.aspx

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.

Visio 2010: How do I change the Title when in Borders & Titles?

Paula DiTallo — Thu, 14 Mar 2013 13:15:21 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/14/152402.aspx

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.

Visio 2010: How do I change the relationship arrows in the database diagram?

Paula DiTallo — Thu, 14 Mar 2013 13:09:19 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/14/152401.aspx

For a quick changeover, go to:

Home -> Database -> Display Options ->Relationship

Select the relationship, then Database Properties -> Miscellaneous.

SQL Server 2012: How do I set "Edit top" rows to a different number?

Paula DiTallo — Wed, 13 Mar 2013 01:27:30 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2013/03/12/152390.aspx

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.

How do I fix the error: CS1548: Cryptographic failure while signing assembly ?

Paula DiTallo — Fri, 11 Nov 2011 15:36:14 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/11/11/147659.aspx

The full error in Microsoft Visual Studio on a compile looks like this:

error CS1548: Cryptographic failure while signing assembly 'C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\StoredProcedures\obj\Debug\StoredProcedures.dll'

This is likely due to a missing strong key pair value file. The easiest way to solve this problem is to create a new one. Navigate to:

Microsoft Visual Studio 2010>Visual Studio Tools>Visual Studio x64 Win64 Command Prompt (2010) [if you aren't on an x64 box, pick another command prompt option that fits]

Once the MS-Dos window displays, type in this statement:

sn -k c:\SampleKey.snk

Then copy the output *.snk file to project directory, or the *referenced directory.

Remove the old reference to the *.snk file from the project.

Add the paired key back to the project as an existing item.

When you add back the *.snk file to the project, you will see that the *.snk file is no longer missing.

Our work is done!

*referenced directory: Pay attention to the original error message on compile. The *.snk file that is referenced may be in a directory path you aren't expecting--so you will still get the error unless you change the directory path or write the file to the directory the program is expecting to find the *.snk file.

How do I run (execute) a .bin file in Linux (Ubuntu) ?

Paula DiTallo — Fri, 11 Nov 2011 01:31:10 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/11/10/147653.aspx

If you are on a desktop version of Ubuntu, you can right-click on the file icon, click the permissions tab and click on "allow execution". If you are on a server copy without the desktop bells and whistles (or you would rather work with a command line in a terminal window), then do the following:

sudo chmod +x myProgram.bin

after you enter your password and get the prompt back type:

./myProgram.bin

How do I remove a named instance of sql server 2008 on the same mssql server?

Paula DiTallo — Thu, 03 Nov 2011 06:28:31 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/11/03/147533.aspx

I recently needed to work with SSIS on my laptop with an existing SQL Server 2008 Express instance. I did not setup the service accounts correctly on the SQL Server 2008 Development install--so needed to remove the corrupt install.

There doesn't appear to be much documentation on this—well, if there is, then I didn't key in the best search criteria! Anyway, the removal of a named 2008 instance is an easy set of steps, although nervous-making--since you'll be uninstalling Microsoft SQL Server 2008 without any initial guidance.

Here are the steps you will need to take:

1. Navigate to Control Panel->Add/Remove Programs

2. Select Microsoft SQL Server 2008

3. Select Change/Remove

4. Select Remove

You will be asked for a specific instance. This is where you type in the name of the instance you want to remove. DON'T get click happy on what to de-install. Click ONLY on the items that specify your named instance. LEAVE all the SHARED features. If you delete them, you'll corrupt your other installations on that server.

How do I create and access another sql server db using linked server in SQL Server?

Paula DiTallo — Mon, 31 Oct 2011 18:42:08 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/10/31/147504.aspx

For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference:

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

Here it is in a nutshell: If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made using this security context" , the remote login/password combo (usually a service account living in active directory) and (3) set the server options rpc,rpdc out,data access, remote collation to True. That's it! No need to worry about provider, connection strings,etc.

Here's an example on how to retrieve data from a table using the linked server reference once it is established from an interactive query window in SMS:

select * from [MYSQLSERVERHOSTNAME].[DBInstanceName].[SchemaName].[TableName]

How do I know what version of Ubuntu I am running?

Paula DiTallo — Wed, 26 Oct 2011 20:47:12 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/10/26/147467.aspx

From the terminal type:

lsb_release -a

How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?

Paula DiTallo — Wed, 26 Oct 2011 19:46:25 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/10/26/147464.aspx

I've been there! You are at a client site and you know you need certain data elements but aren't certain how many databases on a given server ...or ... which tables the data elements you are interested in might appear.

Here's a single statement when executed from any SMS query connection that will get you there:

EXEC sp_msforeachdb '
DECLARE @pattern nvarchar(100)
SET @pattern = ''%elementName%''
IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern)
BEGIN
    SELECT ''?''
    SELECT table_catalog,table_name, column_name
    FROM [?].information_schema.columns
    WHERE column_name LIKE @pattern
END '

How do I get access to DB2 from my .NET applications?

Paula DiTallo — Tue, 13 Sep 2011 18:08:05 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/09/13/146857.aspx

You have at least 2 choices:

Source 1: SQL Server 2008 Feature Pack

http://www.microsoft.com/download/en/details.aspx?id=3522

... this includes Microsoft OLEDB Provider for DB2

Source 2: Available from IBM, is the IBM I Access pack

http://www-03.ibm.com/systems/i/software/access/cafaq.html

Remember, whichever one you use, you will need to specifiy which libraries under the detail/advance settings in ODBC.

Why Won't the Relationship Lines in Visio 2010 Connect to Anything?

Paula DiTallo — Tue, 13 Sep 2011 17:14:33 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/09/13/146853.aspx

That is a very good question..to which only Microsoft has the answer!:-) In the meantime, suffice it to say that every once in awhile, Visio loses track of your Glue/SnapTo settings which you'll need to restore. Here's what to do:

1. In Visio, navigate to "Home", open "View", expand "Visual Aids"
2. Once in "Visual Aids", Check or Re-Check the "Glue"box.
3. Under the "Snap To" category, be sure shape intersections, shape handles, shape vertices,
and connection points are all checked.
4. Under the "Glue To" category, make sure the shape handles, shape vertices,
and connection points are all checked.

That's it!

SQL Server: UPDATE Statement Basics

Paula DiTallo — Mon, 29 Aug 2011 19:30:19 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/08/29/146697.aspx

The last thing anyone wants to do is mess up an UPDATE statement! Here's a quick refresher for those of you using SQL Server:

Simplest case (a single row, in a single table with a single known value in a column):

UPDATE YourTableName
SET Column1 = ‘NewValue’
WHERE SameOrOtherColumn = ‘OldValue’

Most common case (multiple rows, using another table as the source data with multiple columns):

UPDATE YourTableName
SET ColumnX = OtherTable.Column1,
ColumnY = OtherTable.Column2
FROM OtherTable
WHERE YourTableName.Column1 = OtherTable.Column1

Advanced case (multiple rows, multiple tables reliant on a subquery)

UPDATE Target_Table
   SET col2 = z.colB,
       col3 = z.colC,
       col4 = z.colD,
       col5 = z.colE
     FROM
       (select x.col_a,
               x.col_b,
               y.col_b,
               y.col_b
            from tbl_1 x, tbl_2 y
           where x.col_c = y.col_c
        ) z
     WHERE Target_Table.col1 = z..colA

Think relational algebra for this implementation--basically, what you are doing in the subselect is gathering the data you need from the necessary tables, then redefining the results as 'z'. Once you've done that, you are assigning the target columns the values retrieved back from your subquery.

SQL Server: How Can I search for a specific text value in all the stored procedures?

Paula DiTallo — Mon, 29 Aug 2011 19:01:45 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/08/29/146696.aspx

To quickly look through your stored procedure objects for a text value in a database instance, do the following:

use [Metro]
go

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%whatyouarelookingfor%'
AND ROUTINE_TYPE = 'PROCEDURE'

Now, this is no replacement for a configuration management repository, but it will do in a pinch. BTW, remember this sql statement does NOT transcend all databases on the server, just a single db instance.

How do I restore a SQL Server database from a backup *.bak file?

Paula DiTallo — Mon, 11 Jul 2011 23:34:01 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/07/11/146167.aspx

If you aren't sure what is actually in the backup file, or even if you think you are sure--execute this statement from a fresh query window:

RESTORE FILELISTONLY
FROM DISK = 'X:\MDD\Clients\Lg\H4\H4L.bak'

The information about the mdf/ldf files will show up looking like this:

In this example, I was restoring a client's database to one of my servers--so I had to 'MOVE' the location paths to represent the drives I had available. If I were restoring to the same location, I would NOT have needed the 'MOVE' condition.

RESTORE DATABASE HumRes4
FROM DISK = 'X:\MDD\Clients\Lg\H4\H4.bak'
WITH REPLACE, MOVE 'Final_LM_Data' TO 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\HumRes4.MDF',
MOVE 'Final_LM_Log' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\HumRes4_Log.LDF'

Why does Eclipse say Another Transaction is Already in Progress when I Deploy my Google Application?

Paula DiTallo — Sun, 03 Jul 2011 01:32:21 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/07/02/146058.aspx

The message may also say something line:

Unable to update app: Error posting to URL: https://appengine.google.com/api/appversion/create?app_id=metro-stockwatch&version=1&
409 Conflict

Another transaction by user Your.Name is already in progress for app: your-appName, version: x. That user can undo the transaction with "appcfg rollback".

Basically this means that in the midst of your previous deployment of the application, the process was interrupted. The Google SDK interprets this as a deployment that must be rolled back. There is no resolution to this without locating where your \bin directory containing the appcfg.sh or appcfg.cmd file exists in order to rollback the corrupt deployment.

Since I am working on a windows box when this happened to me, here is where I found the appcfg.cmd file:

C:\Program Files\Eclipse\eclipse\plugins\com.google.appengine.eclipse.sdkbundle.1.3.8_1.3.8.v201010161055\appengine-java-sdk-1.3.8\bin\

Look for your application--more specfically, look for a directory called WEB-INF. In my app, WEB-INF resides under the \war directory. Here it is as an example:

C:\Documents and Settings\Administrator\workspace\StockWatcher\war

Open up a windows command window or execute cmd from Run (Start>All Programs; Run; type: cmd). Execute the command: appcfg rollback your-appName by wrapping the location of where the appcfg.cmd file exists, and wrapping the location of root of where you found the WEB-INF directory. This should correct your previously corrupt deployment. See the screenshot below as an example of the command line and of the expected message displays. BTW, if you don't work with dos windows much, remember to include the quotation marks "" as you see them. Windows needs the quote to deal with "Documents and Settings" and "Program Files". If you forget the quotation marks, you may see an error message that indicates a file or directory is not found.

Why does my browser take me to Scour.com? (redirect virus)

Paula DiTallo — Wed, 29 Jun 2011 23:58:10 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/06/29/146023.aspx

The "scour" or Rootkit.Win32.TDSS virus has a long history which can be found here: http://en.wikipedia.org/wiki/Scour

Here is the primary symptom: after searching for something in your web browser using google, one of the results that you click on redirects you to scour.com.

If you've executed ClamWin, Malwarebytes, McAfee, Norton, etc. to find and isolate the virus without any luck--this isn't really a surprise, since this virus attaches to existing system drivers.

I only know of one reliable package that will remove this without ill effects--like adding new spyware. This package is called TDSSKiller. I have seen multiple websites that claim to have this software available, but the one that I know is reliable is located here:

http://support.kaspersky.com/viruses/solutions?qid=208280684

Once you go to Kaspersky's tech support site, the TDSSKiller zip file is available for downloading.

When you execute this software, you will be able to "cure" or repair the infected driver. Remember to jot down the name of the driver for future reference--should you need to reinstall the driver from a "same-as" working computer, or your install disk if the repair is ineffective. The driver that happened to get infected on my computer was the tcpip.sys driver. This caused my win sockets to loose their ip addresses. In most other instances, less critical drivers such as HDAudBus.sys are infected. In my case, I was not through correcting my computer problems until I corrected the broken WinSock issue and loaded an earlier version of the tcpip.sys driver from: C:\WINDOWS\ServicePackFiles\i386 which I placed in: C:\WINDOWS\system32\drivers

Don't forget to reboot your computer after your repair!

Once you download TDSSKiller and cure/repair your infected driver(s), the redirect on google searches should disappear .

What happened to my Entity Framework (edmx) File?

Paula DiTallo — Tue, 14 Jun 2011 22:33:24 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/06/14/145860.aspx

If you're like me, you've probably clicked/clacked, docked/undocked the window with the edmx file while you were working on it in visual studio without intending to--and now, the entity diagram is gone and you are unable to open the file again from the solution window!

Luckily, the edmx file is just another visually displayed xml file. From the solution window, right click on the edmx file -- select "Open with..." -- choose Xml . Once you see the xml for the edmx file close it. Go back to the solution window, then double click on the edmx file. It should now come up presenting the default ER diagram.

Why do I get the error: Specified Named Connection is either not found in the configuration for the EntityClient Provider?

Paula DiTallo — Tue, 14 Jun 2011 20:22:39 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/06/14/145859.aspx

The chances are pretty high that you are creating a multi-tiered application with a solution that may be calling several different projects, one of which is the ADO.NET Entity Framework DAO-layer. In order for the entity frame work to work outside the immediate project that its been created under, you'll have to import the connection string built by the EF wizard at create time to your other project's application or web config file.

Here's an example. I have two projects under a single solution, 1 project is a test driver console app called RetroCar.Test.EF. This project depends on a data access layer project called: RetroCarEntityFramework. Even though RetroCar.Test.EF is just a simple unit test console app without an obvious need for a config file, I still need to import the connection string used in RetroCarEntityFramework in order for the test driver application not to bomb out. The best way to do this is just go to the project RetroCar.Test.EF and add a new application config file item, then cut and paste the connection string from RetroCarEntityFramework into that. Recompile. This should fix the trouble.

If your woes drill down in the trenches of EF further, check out this thread on the topic:

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/f5904b4d-b2f8-421e-90de-339f93959533

System.Data.OracleClient is missing or has been depricated. How do I fix this?

Paula DiTallo — Tue, 07 Jun 2011 21:26:26 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/06/07/145758.aspx

Yes, Microsoft is getting quite a reputation for abandoning or deprecating it's data access products/libraries. One thing you can do is download a 3rd party product called dotConnect by DevArt. The express edition is free.

http://www.devart.com/dotconnect/oracle/download.html

... however, if you are working in a shop that doesn't like its development team to use not well known third party tools, there's an alternative--but it still requires that you go to a non-microsoft source. Use Oracle.DataAccess.dll with the Oracle Data Provider .NET (ODP.NET) client installed. This can be found on Oracle's website:

http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

For now, System.Data.OracleClient still works, however Microsoft's deprecation message on compile is a warning to you that the feature will eventually be phased out completely--and although works, may not actually be supported.

Why is Vista so Slow when Browsing (Surfing) the Web?

Paula DiTallo — Sat, 04 Jun 2011 04:44:40 GMT

Originally posted on: http://geekswithblogs.net/AskPaula/archive/2011/06/04/145710.aspx

Vista has an auto tuning feature which is hit and miss, depending on what network appliances/cards/devices you've got hooked up. Try this:

1. Navigate to Start>Programs>Accessories>MS Command

2. Type: netsh interface tcp show global

3. Look the line for receive window auto tuning. If it says highlyrestricted type this command (all on one line):

netsh interface tcp set global autotuning=restricted

4. Try browsing, if it isn't any better, enter the same command like, just change restricted to disabled. Try browsing again, if there is still no improvement, change disabled to normal.

For those of you interested in the techie details, check out this blog:

TechNet CableGuy