Lovin' the OUTPUT clause...

Change is good, even if it appears big and scary at first…

Ever find a MUCH easier way of doing things and just fell in LOVE with it? It was that way for me once I discovered table valued functions in SQL Server. From that moment on I saw everything as a function “hill” to climb. My happiest moment: turning a query which took over 90 seconds down to 3 seconds because of this discovery and a subsequent re-architecture of all database calls.

Fast forward to… now… the OUTPUT clause. I know, I know, been around since SQL 2005 but until you wrap your head around it, create a new database or have time to re-architect something old, you can’t really get the implications until you incorporate it in a new database design.

One call to a database to update or delete can not only save your data, but OUTPUT the data you want to save in another table for, oh I don’t know, change management, historical, CYA implications.

Simple usage:

DELETE FROM [table] OUTPUT deleted.* INTO [tablearchive] FROM [table1] t1 JOIN [table] ON t1.ID=[table].table1_ID WHERE ID = 123

Double duty – deleting (or updating or inserting for that matter) and saving whatever details from that statement into another table. Saving yourself either an additional call from the front end, or at the very least, an additional SQL statement.

Just keeping it simple.

System.Data.SqlClient.SqlException: Timeout expired

You are most likely getting this because you are leaking connections, a good rule to follow is borrowed from Angel Saenz-Badillos: blog post.

public void DoesNotLeakConnections()
{
     Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5"))
     {
          sqlconnection1.Open();
          SqlCommand sqlcommand1 = s          qlconnection1.CreateCommand();
          sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";
          sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called.
     sql     connection1.Close(); //Still never gets called.
     }// Here sqlconnection1.Dispose is _guaranteed_
}

SQL Server SSIS: The connection "{SSIS Object ID, crazy long string}” is not found.

I would “hanker” a guess you copied a connection into a new SSIS Package, changed the name and successfully ran the package from Visual Studio. Yet, deploying it to the server produced the above error and when you search for the ID of the object the error message specifies, you can’t find it. Am I right? Even with a visual search through the IDs, you are unable to find the pesky ID stated in the error in your package, correct?

If so, try searching the entire solution for the ID – you should find the original object you copied and the search will also give you a hint of what object it was copied to that you need to reproduce.

In the package, do a Edit -> Find and Replace -> Entire Solution and paste in the annoying and mysterious ID in the error message received from the SQL Server Agent job that fails. This search should reveal at least two, most likely four lines where this ID shows up. Select which object is in the .dtsx package which is failing. If you scroll to the right in the Find Results screen, you should find the user friendly name of the object. It will be something like:

</DTS:Property DTS:Name=”ObjectName”>Some Object Name</DTS:Property>

Now you have the user friendly name of the object. You should RE-create (from scratch – do not copy/paste) the connection. Use this new connection in the task(s) necessary, then delete the old connection, rebuild and deploy. Test again, if possible, from SQL Server Agent.

From my research it seems that Visual Studio has issues sometimes with copying connections from one package to another. Sometimes it keeps the old ID in the manifest or logging, and sometimes it even erases the ID and coughs up blood.

Hopefully, helpfully yours… Lara

Connecting to a Web Service, you CAN set timeout on the client...

This information would have been helpful, ohhhhh, a few months back, after countless (and random, of course) timeout occurrences and lots of hair loss and shoulder shrugging.

Background: I built a .net console application that downloads our HR data from a “major” HR provider. This project consisted of security certificates (purchasing and exchanging) on all provider server as well as client server; logins and passwords and research, research, research. The company provided sample code, which basically showed how to query one module at a time and one module at a time.

But my task was to download all the data for all employees every day, including data for terminated employees. We have over 600 active employees, which employee has, obviously, multiple rows in most, and sometimes more than one, in each module of data. I call them modules, for lack of a better term. This equates to downloading approximately 70,000 records once a day. The connection and download takes approximately 9 minutes. Of that 9 minutes I’d say a few are spent waiting for a response, I’d guess that the actual workings take about 5 minutes, not bad I think but I don’t have much to compare it to at this point.

The provider does not provide a “changed” mechanism (or at least they did not tell me they have that mechanism – although many of the hurdles I came across on this journey were questions that when asked multiple times were simple answers – maybe the question just had to be asked of the right person?). Anyway, I stray… One of the first errors I would receive, randomly, would be a timeout attempting to download data from a module. And one particular module would always timeout so that I had to break up the requests by choosing an additional filter.

So, seemed to be pretty stable with a timeout occurring once a week or two, I could handle that, especially since when I asked them the question and sent them the error messages, I was given the response “it’s not on our side”… Time passed, and timeouts increased to a point where I couldn’t get that one module, previously broken down into four filtered requests, to even budge.

I sought empathy from our network engineer to monitor the traffic back and forth and also re-wrote most of the application to save the SQL insert commands to a lengthy string and execute after each module completed, so I wasn’t shooting off single SQL insert statements continually. Still nothing seemed to help. I narrowed it down, with debug printable statements, and could show that there was over a 1.5 minute delay from when a request was sent and a response on that one pesky module. Everything I’d researched stated the timeout value was set inside IIS on the provider server.

After compiling my error list and debug statements into a very convincing e-mail to our HR application provider, I finally received a response from them telling me to up my timeout setting to 5 minutes… Huh?

And this is what you get with someone who learns C# from a book and sample code, over having someone who is an expert mentor you…

Simple addition of this line in each module call to download data:

[ServiceName] proxy = new [ServiceName]();
proxy.Timeout = 300000;

Yep, just that simple. Months of frustration being fixed with 23 characters. Sometimes you feel like an idiot at the end of the day. Live and learn.

This one stumped even Salesforce.com, but I didn’t give up!

Data loader is a free tool provided by Salesforce.com and has a command line interface and can be called from within a batch file. The problem is, within the batch file it forces a change directory so it is next to impossible to call the batch file, within a SQL Job as a CmdExec, from a remote server.

There are plenty of blog questions asking for a solution. I’d even called our highest Technical Support person at Salesforce.com, with a very intruiging “Wow, I don’t think you can do this or if you can I don’t know how you would” response. So reassuring.

I stumbled on a blog post that hinted a solution might be calling a windows scheduled job on the remote server. After pouring over Google solutions for hours (ok, probably closer to a whole day) I managed to incorporate this solution and will outline the steps here, for those who are just that bored or are looking for this insight.

Server A
I needed to put the Sales Force data loader, batch and configuration files, as well as the data files on a stand-alone server (Windows 2008 Server).
Server B
Separately, I had a SQL node (2005) on a Windows 2003 server.

I had to pull in data from sales force through the data loader (which downloads .csv files), pull the data into SQL tables, compare and transform, then push data back through the data loader to Salesforce.com.

The SQL job steps are similar to this:
1 – Set all bit files to zero (ActiveX script)
2 – Pull Data from SFDC (scheduled task)
3 – Check for download completion (stored proc)
4 – Pull into staging database (SSIS Package)
Etc…

I created windows scheduled jobs (using the command line because I needed to initiate backwards compatibility for the scheduled jobs, enabling them to be remotely called). So there are scheduled jobs on Server A calling the batch file which pulls and/or pushes through the data loader command line tool (syntax: schtasks /tn “ScheduledTaskName” /tr “c:\path to batch file” /st 01:00 /v1 (you can change the task once created, remove the scheduled time, the v1 switch is to make it backwards compatible).

Note – Step 1 above: The problem with calling scheduled jobs through SQL Server is you issue the command and it returns, immediately, a success – unless it is unable to start the task. Which means the command has been issued but not necessarily that the batch file which was called completed. This can take minutes, depending upon the size of the .csv you are attempting to pull down or push up. To solve this problem, I begin my series of SQL Steps with an ActiveX script which writes a series of text files with a zero (0) in them, then I have a stored procedure which I pass in the name of the .txt file as the parameter and it loops and waits for 60 seconds, until it finds the 1 in the file.


Step 2 - I run this through a SQL Job – operating system (CmdExec) step using this syntax: schtasks /Run /S ServerName /TN "ScheduledTaskName"

Step 3 – I created a stored procedure which checks the contents of the file, the call would be similar to: EXEC [dbo].[pr_ReadFinishFileBit] '\\server\c$\folder\filetocheck.txt' and the contents of the proc are similar to:

CREATe PROCEDURE [dbo].[pr_ReadFinishFileBit]
(@filetocheck varchar(255))
AS
BEGIN
SET NOCOUNT ON;

set @filetocheck = 'type ' + @filetocheck

DECLARE @output TABLE (bout bit)
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
DECLARE @check bit
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
DECLARE @ctr int
SET @ctr = 0

WHILE @check = 0
BEGIN
SET @ctr = @ctr + 1
IF @ctr = 60 --too long, error out
BEGIN
RAISERROR('Error - download/upload not complete within 60 minutes',16,1)
BREAK;
END
--wait for one minute, check again
WAITFOR DELAY '0:1:00'
DELETE FROM @output
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
END

END

Step 4 – This is a pretty straight forward SSIS package which pulls from the .csv files into a staging database – nothing fancy so I won’t bore you with the details.

If you find yourself in this same situation and need more tedious details, please feel free to post a comment and I’ll attempt to help where I can.

Peace out.

Upgraded JustTheWeather

Because GPS service is intermitent depending on where you are, if you are indoors, etc. I added the ability to enter a city or use GPS with menu options. Still learning, hoping to build an app for Android Developer Challenge II (http://code.google.com/android/adc/ ). Hopefully I'll find the time in the next month to whip something up.

Please post comments with any JustTheWeather application.

Already had to upgrade JustTheWeather

Like any noob developer, my program didn't work for everyone. I believe I've fixed the major problem: uses GPS location instead of last known network location. I also added an end user license agreement, to protect myself - nothing more (in this crazy world).

Please let me know if you encounter any issues by e-mailing me or posting comments to this blog.

JustTheWeather app for Android / Google Phone

I'm playing around with the new Android platform and have produced my first application. It's called JustTheWeather and takes your GPS location and queries the Google weather api for current and forecast conditions, displaying the results with text and images.

I wanted to make a simple app with just the weather so my 10 year old step daughter could look on my phone for her daily question of "What's the weather going to be like today?".

It has been fun and I've learned quite a lot, mostly that I have a lot to learn!

Probably going to publish it to the android market soon, for free of course, it's so simple that I can't imagine charging for it.

This was just a stepping stone (or diving board depending upon how you look at it) into building the application I really want on my Google phone. Shhh.... more to come.

Grant select to all objects of a schema to user or group (SQL 2005)

Longer title for this blog than the syntax:

GRANT SELECT ON SCHEMA::[SCHEMA_NAME] TO USER

Then you could go into the properties for the group or user and add other rights, like execute, update, delete.

SQL Server 2005 - Refresh All Views in All Schemas

The textbook reason to run sp_refreshview: Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

You could turn this into a stored procedure, accepting a parameter for schema (@filterschema varchar(255)) and adding the where clause: where table_schema=@filterschema. Just some thoughts.

T-SQL with error catching for SQL Server 2005 (for example a column was removed from a table but is still referenced in a view):

DECLARE @vtr VARCHAR(1000)
DECLARE viewcur CURSOR FOR
SELECT table_schema + '.' + table_name FROM information_schema.views
order by table_schema, table_name

OPEN viewcur
FETCH NEXT FROM viewcur INTO @vtr
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
EXEC sp_refreshview @vtr
END TRY
BEGIN CATCH
PRINT 'Errors found in view:' + @vtr + ', Error received: ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM viewcur INTO @vtr
END

CLOSE viewcur
DEALLOCATE viewcur

SharePoint + Active Directory + Bogus Accounts = Confusion

SharePoint pulls active directory accounts, easy right? But for my corporation there are tons of accounts that are not actual user accounts, but necessary for normal operation (conference room accounts, guest accounts, etc.) I had to dig through the SharePoint tables to create a solution to display not only the actual user accounts, but parsed into individual divisions for display on separate portal pages (division only names).

Deconstructing SharePoint AD pull...

I created a table, called SP_ADUsers to push data into it in a more understandable structure so I matched fields with what I needed.

The main tables in SharePoint (sharedservices database, named whatever you configured it to be named) are UserProfile and UserProfileValue. I first created a view so I could limit the userprofile ids in which I need to push into my more user-friendly table.

The sql query started like so:
--sameple view sql
select * from [sharedservicesdbname].dbo.UserProfile

This returned over 2,500 records, and after scrubbing out the bogus accounts, I am left with a little over 1,950 valid user accounts. I eliminated accounts by simply adding where clauses, both eliminating actual preferrednames or like ('%tester%') preferred names which would eliminate all tester accounts we utilize.

Next I joined (multiple times) the userprofile table to the userprofilevalue tables in a statement like so:

select Convert(VarChar,upv.PropertyVal), --firstname
Convert(VarChar,upv2.PropertyVal), --lastname
Convert(VarChar,upv3.PropertyVal), --workphone
Convert(VarChar,upv4.PropertyVal), --workemail
Convert(VarChar,upv5.PropertyVal), --office
Convert(VarChar,upv6.PropertyVal), --title
Convert(VarChar,upv7.PropertyVal), --department
Convert(VarChar,up2.PreferredName), --managername
Convert(VarChar,upv8.PropertyVal), --fax
Convert(VarChar,upv9.PropertyVal), --cellphone
Convert(VarChar,upv11.PropertyVal), --company
Convert(VarChar,upv12.PropertyVal), --city
Convert(VarChar,upv13.PropertyVal), --state
Convert(VarChar,upv14.PropertyVal), --country
Convert(VarChar,up.NTName) --login
from [sharedservicesdbname].dbo.UserProfile up
left join [sharedservicesdbname].dbo.UserProfileValue upv
on up.recordid=upv.recordid and upv.propertyid=4 --firstname
left join [sharedservicesdbname].dbo.UserProfileValue upv2
on up.recordid=upv2.recordid and upv2.propertyid=5 --lastname
left join [sharedservicesdbname].dbo.UserProfileValue upv3
on up.recordid=upv3.recordid and upv3.propertyid=8 --workphone
left join [sharedservicesdbname].dbo.UserProfileValue upv4
on up.recordid=upv4.recordid and upv4.propertyid=9 --workemail
left join [sharedservicesdbname].dbo.UserProfileValue upv5
on up.recordid=upv5.recordid and upv5.propertyid=11 --office
left join [sharedservicesdbname].dbo.UserProfileValue upv6
on up.recordid=upv6.recordid and upv6.propertyid=13 --title
left join [sharedservicesdbname].dbo.UserProfileValue upv7
on up.recordid=upv7.recordid and upv7.propertyid=14 --department
left join [sharedservicesdbname].dbo.UserProfileValue upv8
on up.recordid=upv8.recordid and upv8.propertyid=20 --fax
left join [sharedservicesdbname].dbo.UserProfileValue upv9
on up.recordid=upv9.recordid and upv9.propertyid=19 --cellphone
left join [sharedservicesdbname].dbo.UserProfileValue upv10
on up.recordid=upv10.recordid and upv10.propertyid=6
left join [sharedservicesdbname].dbo.UserProfileValue upv11
on up.recordid=upv11.recordid and upv11.propertyid=10001 --company
left join [sharedservicesdbname].dbo.UserProfileValue upv12
on up.recordid=upv12.recordid and upv12.propertyid=10002 --city
left join [sharedservicesdbname].dbo.UserProfileValue upv13
on up.recordid=upv13.recordid and upv13.propertyid=10003 --state
left join [sharedservicesdbname].dbo.UserProfileValue upv14
on up.recordid=upv14.recordid and upv14.propertyid=10004 --country
left join [sharedservicesdbname].dbo.UserProfile up2
on upv10.PropertyVal=up2.NTName and upv10.PropertyID=6
where up.recordid in (select recordid from [viewcreatedabove])

And inside a stored procedure that is run once a day (after the profile import on SharePoint) does the following:

1. Truncates the more userfriendly table I created for dataviews within SharePoint (SP_ADUsers);
2. Fills the table with the values returned from the select statement above.

Now I have a nightly updated, scrubbed active directory user list which doubles as our phone directory.

IE Crashes when accessing a file from SharePoint

Scenario:
One day a user can click on a hyperlink to a document (word, excel, one-note) in SharePoint and everything is great.

Next day the user gets an Internet Explorer error message or simply crashes trying to access the same document.

Cause (or at least my opinion on the cause):
The user computer has two versions of office documents, most likely 2003 and 2007. When office updates are applied (sometimes automatically if you have automatic updates on, or by SUS server pushes) the dll (OWSSUPP.DLL) is overwritten with incorrect information or is corrupted.

Fix:
Easy actually, follow the instructions below...

1. Open Computer: C:\Program Files\Microsoft Office\Office12

2. Find and Delete (putting it in recycle bin) this dll: OWSSUPP.DLL

3. Run Office Diagnostics by: Start -> All Programs -> Microsoft Office -> Office Tools -> Microsoft Office Diagnostics -> Run the Diagnostics

4. When complete, close Diagnostics and attempt to access a task list or open a document within SharePoint Portal.

This should fix the issue. Good luck.

SQL 2005: Change object schema

So simple that I usually forget the syntax...

USE [WHATEVERDATABSE]
GO
ALTER SCHEMA [NewSchemaName] --change to schema
TRANSFER [OldSchemaName].[ObjectName]
GO

SharePoint - Default Value for connected DataView webpart

Scenario (MOSS 2007):

I have a listview (standard format) connected to a dataview. Connection is source web part and provides row to target web part, which gets filter values from.

In essence, if you click on an item in the list view, I want to see a few particular fields in the dataview.

In this particular case, the listview was a list of open projects or tasks for members of our network group, and the comments were too long to display - it made the rows gy-normous and basically unreadable - also making the page stretch so long you had to scroll to eternity to get to the bottom of the list.

The culprit field was "comments" so we removed it from showing on the list. Now the title of the project, along with the comments, could be shown in the top webpart which was the dataview getting filter value from the listview.

Problem: When a user first opened the list, the first item (ID=1) would show the comments. It was as if the default value for the page was the first record. We did not want this behavior.

Solution: After some research on the web, I found musings about sending parameters in querystring. I also noticed that when I selected an item, the page was refreshed and a querystring parameter was being sent as: &selecteditem=... (number ID of task I clicked).

Steps:

1. Add a parameter:
• Under Common Data View Tasks (click the little boxed arrow on the right of the dataview when hovering over the dataview), then click on Parameters... link.
• Select New Parameter, I named it RecID, set Parameter Source to Query String, Query String Variable to SelectedID, Default Value: 0
• Click out of the Parameter menu and select Filter: in the Common Data View Task pop-up.
• Click in the field name, choose field (for my list I choose ID), left the comparison as Equals, then Value my new parameter of [RecID] was in the pull-down list

Now the default filter value for my dataview webpart was set to nothing (no 0 record existed in my list).

Sharepoint - Listview and enabling connections to other webparts

A colleague came to me and asked me to help her with more advanced functionality in SharePoint. She created a list and it contained too much data to display easily. It was suggested to her to make a small text box somewhere that, once a user clicks on an item in a list, the box or area is filled in with the list field, in this case a comment field.

She already tried creating a webpart and using the "Connections" ability without success. It was grayed out.

After some researching and testing, I started to believe her list (which appeared as a datasheet view and had the Access Web Datasheet icon in the upper left hand corner) format may be the culprit.

When I chose to display the list in Standard View, the connections menu item was enabled. I imagine, because I don't have time to do the research, that the Access Web Datasheet may not allow interaction to other webparts, but this is just a theory. I found no such information in a search through the web.

To see how I enabled the dataview webpart to default to an empty record upon the original page loading, see my next blog.