Developing Matt

My Technical Journal

32 bit Twice Shy

leave a comment »

 

If you sql server is running 64 bit and your ssis is using something other than a 64 bit provider you might receive an error with your job. 

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered — perhaps no 64-bit provider is available Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2012-01-18 11:58:16.53     Code: 0xC020801C     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. 

Change the step on your job (the one executing your ssis package) to Use 32 bit runtime (Execution options tab).  Jet is not 64 bit, therefore you have to change your step to run it as a 32 bit application.

ssis32bit

Written by matt

January 18, 2012 at 5:37 pm

Posted in SSIS

Sensitive SSIS and Encryption

leave a comment »

If you ENCRYPT SENSITIVE DATA WITH USER KEY everything will work fine until you import and run the job.  You might get:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

If you set it to be a password (ENCRYPT SENSITIVE WITH USER KEY)) and don’t set up that password when setting up the job you will get:

Failed to decrypt an encrypted XML node because the password was not specified or not correct.

Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user .". 

The way I like to do it is to set it at ENCRYPT SENSITIVE WITH USER KEY which will enable the passwords to be saved in the package for development and upon import change the encryption to use Rely on server storage and roles for access control which will use the permissions inherent to the job owner.

New Picture (8)

Written by matt

January 18, 2012 at 5:20 pm

Posted in SSIS

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

Maximum request length exceeded and helping users past Microsoft Speak

leave a comment »

Using file attachments can cause problems if your file attachments are larger than designated.  By default the designated amount is 4MB.  This can be modified in the web.config.  I have it set up to allow a 20 MB file

       <system.web>
         <httpRuntime maxRequestLength="20480" executionTimeout="200" />
       </system.web>

If the user exceeds the size dictated in the web.config they will receive the following. 

Server Error
Maximum request length exceeded.

Most users don’t understand Microsoft speak, so it is up to you to help them.  To deliver a friendly message you could write your own tool to upload but that would be a lot of effort for something microsoft already does for you.  The problem is that the error occurs at the application level, not the page level. You can Try Catch all day long but they will never get your friendly popup that you worked so hard on. 

The elegant solution is to add something to the Application_Error routine in the global.asax.  I catch the error and forward it to an Error page, passing along my own querystrings (error=filelength and jobid…something pertinent to my app)

    Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
        Dim x As Exception = Server.GetLastError

        If x.InnerException.Message = "Maximum request length exceeded." Then

            Server.ClearError()

            Dim jobid As String = Context.Request.QueryString.Item("jobid")

            Response.Redirect("Error.aspx?error=filelength&jobid=" & jobid, True)

        End If

    End Sub

In the Error.aspx page I catch the query strings and pass along the pertinent information.  I set it up to enable the catch of other errors at the application level, but so far this is the only error I catch.

Code Behind:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Request.QueryString.Item("error") = "filelength" Then lblFileSize.Visible = True

        linkReturn.NavigateUrl = "job.aspx?jobid=" & Request.QueryString.Item("jobid").ToString

    End Sub

HTML Markup:

    <form id="form1" runat="server">

        <asp:Label ID="lblFileSize" runat="server"

            Text="There was an error with the attachment. The file size is too big (greater than 20 MB). 

Please find another way to transfer your file (shrink and/or zip your attachment, for example)."

            Font-Size="Large"></asp:Label>

        <br />

        <br />

        <asp:Label ID="Label1" runat="server" Text="Thank you." Font-Size="Large"></asp:Label>

        <br />

    <br />

    <asp:HyperLink ID="linkReturn" runat="server" Font-Size="Large">Click here to return</asp:HyperLink>

    </form>

Thank you Austin Rasmussen for your post here.  Yours was the most helpful page out there that assisted me in this venture.  I adopted a slightly different code as my error was not at the HttpException, but at the Exception namespace instead. 

Written by matt

January 16, 2012 at 4:56 pm

Posted in Asp.net

What’s the umbrella for? (part 2)

leave a comment »

Earlier I wrote a post (What’s the umbrella for?) It seems like I’ve written a couple of posts regarding searching in the database for objects with text in them.  Here is the latest way I’ve done it.  This method seems to return more results.  I should script it all out and make it fancy but I needed to get to the end result instead of wasting company time on the process.

So in order to search all objects in a database for the word FADRIZZLE I first get a list of the databases with

Select name from sys.databases order by name

I paste these names in to Excel, which I think has two L’s but I’m not sure.

Then I use this at the top of my spreadsheet

create table #ttDbList (DatabaseName varchar(50), ObjectName varchar(100))

and add the formula against my database name list

="insert into #ttDbList select ‘" & A2 & "’ as db, o.name  from " & A2 & ".sys.sql_modules m  inner join " & A2 & "..sysobjects o on m.object_id=o.id where [definition] like ‘%FADRIZZLE%’"

If it is a 2000 database I use the following formula instead

="Use " & A2 & "; insert into #ttDbList  SELECT ‘" & A2 & "’ as ‘database’, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%FADRIZZLE%’ "

Copy and paste the results to your query window.

Now you can run it and it will query all the databases on the server for your special word FADRIZZLE.  You’ll get errors for offline databases and it doesn’t discriminate against comments.

Written by matt

January 15, 2012 at 3:28 pm

Posted in T-Sql

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