32 bit Twice Shy

 

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: 0×00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0×80040154  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.

Sensitive SSIS and Encryption

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.

Write to a text file without xp_cmdshell

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.

Unicorns are MAGIC!

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.

Database Info in HTML format

Someone gave this to me back in 2007.  I don’t know the source.  It doesn’t completely work and needs some fixing, but it gives you everything you ever wanted to know about a database in html format – users, tables, views, sprocs, etc.  It will alleviate you having to search the database for everything yourself.   Run it and output results to a file ending in html, then open it.  Even with the errors you can get an idea of what it is doing.  Some rainy day I’ll need this and then I’ll fix it and you will see it’s worth, which is most of the stuff in my garage.

This is akin to me finding a broken something in the attic that I made from my childhood and trying to convince you it’s worth something.    Enjoy!

link

If you can’t obtain dbschema_tables_info

 

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:

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

Nil on Sending Nil

Some things fall into a category that I don’t know how to explain.  I struggled for some time with a project that was sending some data using a web service method call.  The problem arose: I needed to send a null value in one of the parameters and unfortunately the documentation was so poor on this web service.  It didn’t discuss how to pass a null value.  That is to say, there was nil on how to send a nil.

After some time agonizing over this I was able to use a different application to call the webservice without the use of the parameter, which enabled me to track what was being sent.

So for this particular web api the way you can do it is by adding another property to the node and leave the value empty:

        <ParameterName xsi:nil="true"/>

Calling Reports via URL take II

Previously I had posted advice here on how to link to a report via url.  I have some more information for you to assist you (me) with this venture.  Some organizations choose to use IIS rather than use just the web service URL to enable the use of sharepoint as a UI.  Consequently the directory takes a funny look: 

Previously the link looked like this

http://SERVERNAME/ReportServer/?%DIRECTORYNAME%REPORTNAME&rs:Command=Render

However, if you enable viewing the report from sharepoint it has to be fed from IIS.  Therefore the DirectoryName becomes root website followed by the directory, etc.  Also, note, that SERVERNAME can be the servername but it IS the sqlserver name:

http://servername/ReportServer/?http://website/Directory/Reports/ReportName.rdl&rs:Command=Render

One thing to note, there is no way to go to the website directly.  You have to fully qualify the server name.  Confused yet?  If interested here is the configuration of the reports:

So here is the full range of possibilities and examples:

Render to PDF using HTML Report Viewer with Sharepoint Report
http://servername/ReportServer/?http://website/Directory/Reports/ReportName.rdl&rc:Parameters=false&Parameter=ParameterValue&rs:Format=PDF&rs:Command=Render

Render with SharePoint Viewer (nicer look than HTML)
http://website/Directory/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Directory/reports/ReportName.rdl&rv:ParamMode=Hidden&rp:Parameter=ParameterValue

Render Directly to PDF with HTML Viewer
http://servername/ReportServer/Pages/ReportViewer.aspx?http%3a%2f%2fwebsite%2fDirectory%2fReports%2fReportName.rdl&rs:Command=Render&rs:Format=PDF

Render using HTML Viewer
http://servername/ReportServer/Pages/ReportViewer.aspx?http://website/Directory/Reports/ReportName.rdl&rc:Parameters=false&rs:Command=Render&Parameter=ParameterValue

Thanks Chris for your assistance with this stuff!

The Whacks behave differently, unless you give them some action.

Had an interesting  situation where utilizing http://websitename/ behaved differently than http://websitename/default.aspx even though the default.aspx page was being used in both instances.  I was using a third party ( infragistics ) grid that was depending on a client script to sort.  When I sorted the page from http://websitename/ it failed, gave me an error stating ‘instance not found’ in the javascript.  When I sorted from http://websitename/default.aspx it worked without a hitch.

At first I thought it was a cacheing issue…that my scripts weren’t being downloaded on even a hard refresh.  But such was not the case.  After some deliberation I turned to senior developer Chris.  He thought for a while and eventually told me to add an action property in the form tag like thus:

<form id="form1" action="default.aspx" runat="server">

This worked.  It enabled the scripts to find default.aspx and utilize the page properties.  I asked for an explanation. Developer Chris said it had something to do with the new IIS 7 and how it has some feature which enables the whacks (/) to behave differently…and to change this behavior would require some add-in on this IIS console. 

If you view the source of a page and look at the action property it will usually be action=”” which isn’t helpful for a script looking for it.  So by specifying the action you can leave the whacks as they are.

 

Pivot Without Aggregation or Aggravation

I needed to flip my data horizontally.  I didn’t need to aggregate anything.  I was just working with a user that wanted the data to look different.

Instead of this

Car	Tire
Car	Window
Car	Wipers
Car	Engine
Car	Air Freshener

 

He wanted

	col1	col2	col3	col4	col5
Car	Tire	Window	Wipers	Engine	Air Freshener

 

The pivot command requires an aggregate.  It’s the basis of the command. When you are pivoting you are saying ‘hey give me some number crunching for this particular arrangement of data:

PIVOT (calculation(on what) for the following column headings (column A, column B, column C) as pvt

So how can this be done without aggregating?  Well if your group has only one calculation then you can do whatever calculation you want.  For example, the average of 62 will always be 62.  So using the rownumber in conjunction with a max on a string we can cheat and get the pivot command to give us what we want.

I was so close to figuring this out when I found a great post by Arie Stern here.  I used his post to get this concept working.  Thank you Mr. Stern for your assistance.

Set up your table here for this example:

create table #rrr(vehicle varchar(20), part varchar(20))
insert into #rrr values(‘CAR’,'Tire’)
insert into #rrr values(‘CAR’,'Window’)
insert into #rrr values(‘CAR’,'Wiper’)
insert into #rrr values(‘CAR’,'Engine’)
insert into #rrr values(‘CAR’,'Air Freshener’)
Select * from #rrr

The way to pivot this data is as follows:

select vehicle, [1], [2], [3],[4],[5]
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr)
a
pivot (max(part) for rownum in ([1],[2],[3],[4],[5])) as pvt

Note that I am using the rownumber to get my column headings and am using max for the part types.  So this works great, of course, only if you know how many rows there are per vehicle.  Restated: the above works only because I know there are only 5 rows of data. Data is hardly this friendly.  So the catch is: if you don’t know how many rows you will have you will have to build your sql out of dynamic sql.  If you are careful you won’t be writing dynamic sql inside of dynamic sql which will either drive you mad or will drive the person mad behind you that has to maintain your code.  Regardless, here is how you would do it.  :

———-step 1: set up your column names based upon rownumber———————-
declare @rownum varchar(50)
set @rownum = ”
select @rownum = @rownum + ‘[' +  cast(rownum as char(1))+ '],’
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr)
a
set @rownum = left(@rownum, len(@rownum)-1)
print @rownum
–Note that i’m using brackets around column headings.  You could add a more descriptive
–column name but will have to cast your rownumbers later on (Just wanted to keep it as simple as possible).
–Also, note that you have to strip the last comma from your column list

———-step 2: create and call your dynamic sql using your new rownumber variable———–
declare @sql varchar(1000)
set @sql =
‘SELECT vehicle, ‘ + @rownum + ‘
from
(Select vehicle, part, Row_number() over (partition by vehicle order by vehicle) as rownum from #rrr
) a
pivot (max(part) for rownum in (‘ + @rownum + ‘)) as pvt   
‘
print @sql
execute (@sql)