Developing Matt

My Technical Journal

Archive for the ‘Sql Server’ Category

Single Quotes in Outlook to SQL. subtitle: how to go from curly to strait

leave a comment »

I’ve long wished that the single quotes in outlook and word would easily translate to SSMS. On my blog, in my emails, in word, on my bathroom wall, all over the place, you can find the single quote that doesn’t work in the query window. Copy and paste and replace the quotes with quotes. I’m not sure who had the bright idea to make single quotes fancy. Possibly someone with a little more time on their hands. They probably had a mustache.

Regardless, all my whining took a nose dive when I discovered that there is actually an option to not use those fancy curly single quotes.


Yes. Deep in the cavities of options you can find a way to keep outlook, word, and others from replacing the normal single quotes to fancy ones.

Written by matt

March 15, 2012 at 1:07 pm

Posted in Sql Server

Your job owner owns more than you

leave a comment »

The job owner matters.

When you run your Sql Server job that executes your SSIS package or a job creating files or a job inserting to an access database or a job accessing files you may get something akin to

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

This means that you need to find an account and set the owner of the job to that account.  It could be system or NT AUTHORITY\SYSTEM or whatever your sysadmin dictates. 

You would think that the ‘execute as’ would control who is running the show, but this is not the case.  ‘Execute as’ is just the Carney pulling the levers.

Written by matt

January 17, 2012 at 4:32 pm

Posted in Sql Server, SSIS

Write to a text file without xp_cmdshell

leave a comment »

There are dangers of xp_cmdshell.  If turned on you can execute some pretty nasty commands.  Combine that with a poorly written dynamic sql procedure and you have a hackers delight.  It’s a window to the windows of the machine’s soul.

Many places don’t turn it on, and attempts to use it will give you:

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

I don’t blame them.  Unfortunately it takes out an opportunity to combine it with bcp and write to a file. 

The way you can bypass this is by using a job, adding a step that has a cmdExec step that executes bcp to write to a file. 

bcp "select field1, field2 from server.database.dbo.table"  queryout "c:\test.txt" -T –c

bcp bol

I’m still trying to understand ole automation, which isn’t turned on either.

Written by matt

January 14, 2012 at 5:57 pm

Posted in Sql Server, T-Sql

Unicorns are MAGIC!

leave a comment »

What is the difference between unicode and non-unicode character types?  Which one should I use? Nvarchar (unicode) or Varchar (non-unicode)?

If you can use non-unicode use it as it will be smaller and quicker (generally 70% smaller footprint), but if you need things like é or support other languages use unicode.

Unicode is the UNIVERSAL encoding system.  This is supported by a non-profit organization (Unicode Consortium) to ensure that we can all understand each other clearly…not just internationally but historically as well.

To help you remember, the N in front of nvarchar or nchar stands for the National language character set (i.e. unicode).  If your data structure might possibly expand to other languages, set it up to use a unicode datatype. 

If you work with ssis at all you have likely seen the error "Cannot Convert between Unicode and Non Unicode String Data Type"
If you are going from non-unicode (varchar or char) to unicode (nvarchar or nchar) you will benefit transforming the data with the  unicode string [dt_wstr] or unicode text stream [dt_ntext] depending on how lengthy the field is.. 

There is no reason for such a silly title or for the picture below except to make you think this post would be something interesting to read.  There is no relationship between unicode and unicorns, nor will there ever be.

Written by matt

January 13, 2012 at 6:32 am

Posted in Sql Server, SSIS

If you can’t obtain dbschema_tables_info

leave a comment »


After creation of a new linked server (from 2008 sql box to 2000 sql box). I tested my select query and received

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERNAME". The provider supports the interface, but returns a failure code when it is used.

KB article here suggests an update to the 2k box. 

Pinal Dave suggests something that doesn’t apply to me here

For me, I was unable to update the sql box so I had to depend on openquery (link).  Openquery is pretty picky on it’s syntax, so if you get this

"Deferred prepare could not be completed."

Try applying a few brackets:

FROM OPENQUERY(linkedServerName,’select *   FROM [linkedservername].[database].[dboorwhateverschema].[table]’)

Written by matt

December 29, 2011 at 7:12 am

Posted in Sql Server, T-Sql

What Itzik Taught me Part 3: Ordering Your Relations

leave a comment »

A relational database isn’t called so because of how it relates to other tables.  It is called so because it is based upon set theory.  It is centered around the mathematical term relation which emphasizes how things should be grouped (or set) together.  A relational table has like items, that’s all.  This is why you can’t add an order to a view or a derived table.  The concept of ordering is in direct contrast to the relation concept.

For example, think of how you would order your family members.  For me, I would order myself first.  I would place myself first in order of importance.  Mother might wrinkle her nose at this.  Brother might disagree and want to fight me over it.  Father might wait and see how the fight turns out and laugh at us on the lawn, two middle aged guys fighting over who should come first. 

There is no ‘first’ in relation.  We are all equal.  Even if your mother likes putting her flowers in a toilet in her front lawn, you aren’t better than her.  You should still call her every week and ask how they are growing.

However, you can take the group, query a common value and order the result.  Everyone would agree that if we ordered my family in descending order by birth date that I would come first.  No one can argue that.  I’m definitely first there.

This concept is why the engine balks when you attempt to order a view.  A view is a direct representation of the data…data that is organized in groups.  When you select top 100 % …order by 1 you are cheating the system and cheating yourself.  There isn’t any guarantee that it will be ordered because this is not the query giving you an order of the data.  It is a operating as a filter at this point, giving you the top x of data sorted by column 1.  It is saying ‘Well, if column 1 is sorted alphabetically, the top x rows of data are…”

Thanks Itzik for speaking these concepts over and over again to people like me.  i needed to hear it a few times before it sunk in.

Written by matt

June 25, 2011 at 3:41 pm

Posted in Sql Server

Tagged with

linked server alternate setup to sql server

leave a comment »

There are different ways to set up a linked server, but if there is already a connection to a sql server with a linked server it is sometimes not a good idea to mess with the existing one.  Unfortunately you can’t pick Sql Server and add another one.  However you can still use sql server and just pick ‘other data source’ as below:

New Picture (1)


This will, of course, require you to reference the linked server name when calling out to your tables and whatnot…so I recommend you GET CLEVER.  A few suggestions:

Pickle Von Corndog 
Ninjastar Dangerrock
Boo Manchu
Watts In A Name
Geez Louise
Mr. Pickles

Written by matt

March 30, 2011 at 2:41 pm

Posted in Sql Server