SSRS report throws an error that says a parameter doesn’t exist when the report designer clearly shows the parameter exists

The order in which the parameters appear in the BIDS Report Data tab can be the difference between your report executing and it failing. Failure to pay attention to this will give you very mysterious error messages!

"The Value expression for the query parameter PARAMNAME contains and error:The expression that references the parameter PARAMNAME does not exist in the parameters collection. Letters in the names of parameters must have the correct case"

It may be visible at design time but not come into scope when the particular expression is evaluated!

You can force the order of the parameters by going into the XML code and adjusting as needed.

Posted in Frustrating errors, SQL | Tagged , , | 12 Comments

Downloading the Results of a Twitter Search using PowerShell and SQL (part 3)

Part one of this series is here and part two is here. You’ll need the SQL scripts from part two in order for the code below to work but you can skip part one unless you’re curious to see the evolution of this project.

First off, I found a bug in my code. A unique contraint violation may be thrown if the exact same tweet is tweeted at the exact same time by two different users. I didn’t code to handle this because I thought it was an incredibly unlikely scenario but given the popularity of automatic tweeting – eg, “Share this on Twitter” buttons – it seems like it is a scenario which can and does happen.

Anyway, I decided to create a different constraint. Here is the code to drop the old one and add the new one.


use twitter
go

/*
a web app which auto tweets for a user 
might cause two identical tweets 
at the same second
*/

if exists (select 1 from sys.indexes where name = N'uq_tblTweet_tweet_tweetDate')
	alter table tblTweet drop constraint uq_tblTweet_tweet_tweetDate

alter table tblTweet
add constraint uq_tblTweet_tweet_tweetDate_twitterUserId unique (tweet, tweetDate, twitterUserId) 


Secondly, I added some paramters to the script. The parameter -searchString takes a single string to search on. Or you can specify a file path as an argument to the -inputFile parameter and it will go through each of the strings and do a search on those strings.

If you use both parameters it will do the -searchString first and then the -inputFile

I needed to update the proc which moves data from the staging table to the data tables so that the staging table doesn’t grow indefinitely. I like the data to hang around for a bit in case I need to debug some error so I don’t want to truncate the table every day. I decided to add a paramater to the proc so that it deletes data whose import date (not the twitter date but the date the data was imported) is more than a certain number of days old. The number of days is passed as an argument on the command line of the PowerShell script call. The default is 30 days for both the PowerShell script and the stored proc and the paramter is named -delAgedStagingData. If you pass a 0 it will not delete any data

Here is the updated proc:


use twitter
go

if object_id(N'uspLoadTwitterDataFromStaging') is not null
	drop procedure uspLoadTwitterDataFromStaging

go

create procedure uspLoadTwitterDataFromStaging
/*
If data is oldet than @deleteDataAge in days, delete it
default = 30
if 0 then don't delete data
*/
@delAgedStagingData int = 30 
as
begin


	insert into tblTwitterUser (twitterUserName)
	select twitterUser
	from tblTwitterStaging
	except
	select twitterUserName
	from tblTwitterUser


	/*
	I could prime this table in a config step 
	but I want the whole thing to be driven by user configurable files in the end product
	or parameters on the command line

	Anyway, the PowerShell script is responsible for inserting the search string into the staging table
	*/

	insert into tblSearchString (searchString)
	select searchString
	from tblTwitterStaging
	except 
	select searchString
	from tblSearchString


	--there is some weird date stuff going on - salvage what you can!
	update tblTwitterStaging
	set twitterdate = convert(datetime,left(twitterdate,10))
	where isdate(twitterdate) = 0


	insert into tblTweet(tweet,tweetDate,twitterUserId)
	select distinct stg.tweet, convert(datetime,stg.twitterDate), u.twitterUserId
	from tblTwitterStaging stg
	inner join tblTwitterUSer u
	on stg.twitterUser = u.twitterUserName

	except select tweet,tweetDate,twitterUserId
	from tblTweet


	insert into tblTweetBySearchString(tweetId,searchStringId)
	select t.tweetId, s.searchStringId
	from tblTwitterStaging stg
	inner join tblTweet t
	on stg.tweet = t.tweet
	inner join tblSearchString s
	on stg.searchString = s.searchString
	except select tweetId,searchStringId
	from tblTweetBySearchString





	if @delAgedStagingData <> 0
		
		delete tblTwitterStaging where datediff(day,importDate,getdate()) > @delAgedStagingData
	
		
end

I also found one case where the published string came back with a format that SQL server couldn’t handle. If I dropped off the time then it could convert. That causes a bit of data loss but I figure it’s better than losing the record and it seems like the situation is pretty rare.

Thirdly, I decided for ease of use to do the URL encoding in the PowerShell script. So when you pass in a string you simply type the search string as you would into any search box. So, if you want to search for “i am the cloud” you can just pass that string as a paramter.

Here is the updated PowerShell script.



param([string]$searchString = "", [string]$inputFile = "", [int]$delAgedStagingData = 30)
Write-Host "searchString: $searchString"
Write-Host "inputFile: $inputFile"
Write-Host "delAgedStagingData: $delAgedStagingData"


[System.Reflection.Assembly]::LoadWithPartialName("System.Web") | out-null
$searchString = [System.Web.HttpUtility]::UrlEncode($searchString)



Function getSearchPage ($searchString, $page)
{
	"searchString = $searchString"
	"page = $page"


	$req = "http://search.twitter.com/search.atom?q=$searchString&show_user=1&rpp=100&since_id=1&page=$page"
	
	
	"`$req = $req"
	
	([xml](new-object net.webclient).DownloadString($req)).feed.entry |
	%{

		#replace problematic characters in input
		$title = $_.title -replace ("`t","``t")
		$title = $title -replace ("'","''")
		$title = $title -replace ("`n","``n")
		$title = $title -replace ("`r","``r")
		$title = $title -replace ("`$","``$")
		$title = $title -replace ("`"","`"`"")
		$published = $_.published -replace ("`t"," ")
		$published = $published -replace ("'","''")
		$published = $published -replace ("`n","``n")
		$published = $published -replace ("`"","`"`"")
		
		
		
		
		$splitData = $title.split(":")
		$twitterUser = $splitData[0]
		
		$tweet = $title.substring($twitterUser.length,$title.length - $twitterUser.length)
		if ($tweet.length -ge 2)
		{$tweet = $tweet.substring(2,$tweet.length -2)}
		
		

		
		#turn input into sql statement
		if ($tweet.length -gt 0){
			$sql = "insert into twitter.dbo.tblTwitterStaging 
			(twitterUser,tweet, twitterDate,searchString) 
			select '$twitterUser','$tweet' ,'$published','$searchString'" 
			
			sqlcmd -E -Q $sql 
			

		}

	} 

	
}

Function getLast10SearchPages ($searchString)
{
	for ($i=10; $i -ge 1; $i--)
	{



		$i

		getSearchPage $searchString $i



	} 
}


#process command line searhc string first
if ($searchString -ne '') {getLast10SearchPages ($searchString)}

#process any search strings in the file
if ($inputFile  -ne '') {Get-Content $inputFile | Foreach-Object {getLast10SearchPages ([System.Web.HttpUtility]::UrlEncode($_))}}



sqlcmd -E -Q "exec twitter.dbo.uspLoadTwitterDataFromStaging @delAgedStagingData = $delAgedStagingData"



I’ve been calling the file twitter_search_to_db.ps1 and assuming that you call yours the same thing here are some sample calls. I created a text file called search_strings.txt which sits in the same directory in which I’m running my script. Here is what is saved in search_strings.txt:

#powershell
#sqlhelp
i am the cloud
death cab for cutie
florence + the machine
mary timony
Jolie Holland

Here is the call with a search string:

.\twitter_search_to_db.ps1 -searchString "J. Vernon McGee"

Here is a call with a file parameter:

.\twitter_search_to_db.ps1 -inputFile ".\search_strings.txt" -delAgedStagingData 30

And here is a call with both:

.\twitter_search_to_db.ps1 -searchString "J. Vernon McGee" -inputFile ".\search_strings.txt" 

Notice that the search string is stored URL encoded in the tables:

select * from twitter.dbo.tblSearchString

I figure that this is closer to what is actually required by the program. It should be fairly safe for use in SQL and over the web. It’s up to the interface to transform user input into something computer friendly. You could store both inside but I try to avoid repetivive data and since the unencoded data should be fairly easy to get from the encoded data it seems silly to have both. You might end up with difficult to find bugs if you have both.

I would recommend you add this as a scheduled task. That’s fairly easy to do. You can find details on that here along with some other nice PowerShell tricks.

I hope to play around a bit with displaying the results but my plan for my next post is probably going to be some pure T-SQL stuff. I feel like I could go on and on with this project. I love PowerShell but my deeper interest in T-SQL is starting to pull me away. So I’m going to take a brief PowerShell vaction.

Posted in PowerShell, SQL | Tagged , , , , , , | Leave a comment

Downloading the Results of a Twitter Search using PowerShell and SQL (part 2)

I’ve expanded a little on my Twitter project. (See part 1 here.) I adjusted the PowerShell script to page through the data going back as far as Twitter holds the data. There is still a lot to be done.

Left to be done:

  • allow for dynamic search strings instead of hard coding
  • read search strings from file or command line
  • better structure in the script

But today I want to focus on the data model. I pasted the commented DDL statements below


--assuming you have created a database for this project. I'm calling mine simply twitter
use twitter 
go

/*

staging - dump all the strings in hear then clean them with T-SQL

*/


create table tblTwitterStaging(
	staging_row_id int identity
	/*
	I'm making the twitterUser and tweet columns much larger than
	the twitter app allows because this is a staging DB and I want to be sure that
	I can handle any mess that twitter sends me
	
	As a general rule, I try to code as defesnively as the situation allows
	
	
	*/
	,twitterUser nvarchar(1000)
	,tweet nvarchar(1000) 
	
	--use nvarchar to avoid differences in formatting between their system and SQL server.
	,twitterDate nvarchar(25) 
	
	--the actual date the row is inserted into the staging table
	,importDate datetime 
	constraint df_tblTwitterStaging_importDate default getdate()
	
	--search string sent to twitter
	,searchString nvarchar(1000) 
	
)


/*

Since a user can have multiple Tweets across many searches it seems good to give them a table

*/

create table tblTwitterUser(
	twitterUserId int identity
	--code defensively - more than twitter will allow
	,twitterUserName nvarchar(450) not null 
)


alter table tblTwitterUser
add constraint pk_tblTwitterUser primary key (twitterUserId)

/*
I can't see how twitter could allow dups in user name
So let's put that in our data model

We may be able to use knowledge like this later for query optimization and so forth
*/
alter table tblTwitterUser
add constraint uq_tblTwitterUser_twitterUserName unique (twitterUserName) 

	
/*

I'm going to scale this out to track multiple searches

*/


create table tblSearchString(
	searchStringId int identity
	,searchString nvarchar(450)
)		


alter table tblSearchString
add constraint pk_tblSearchString primary key (searchStringId)

--We control the search strings and it doesn't make any sense to have multiple, identical search strings
alter table tblSearchString
add constraint uq_tblSearchString_searchString unique (searchString)



create table tblTweet(
	tweetId int identity
	,twitterUserId int
	--once again, much longer than we'd ever expect a tweet to be but coding defensively
	,tweet nvarchar(400) 
	,tweetDate datetime
)

alter table tblTweet
add constraint pk_tblTweet primary key (tweetId)

/*
I don't care about duplicate tweets
if the same tweet shows up in more than one search it will tie back to this one
*/
alter table tblTweet
add constraint uq_tblTweet_tweet_tweetDate unique (tweet, tweetDate) 

--every tweet ties to a user
alter table tblTweet
add constraint fk_tblTweet_tblTwitterUser foreign key (twitterUserId) references tblTwitterUser(twitterUserId)

/*
each search will return zero or more tweets

The same tweet could be returned by more than one search string
So this relationship needs its own table
*/
create table tblTweetBySearchString(
	tweetId int not null
	,searchStringId int not null
)		

alter table tblTweetBySearchString
add constraint pk_tblTweetBySearchString primary key (tweetId, searchStringId)

alter table tblTweetBySearchString
add constraint fk_tblTweetBySearchString_tblTweet foreign key (tweetId) references tblTweet(tweetId)

alter table tblTweetBySearchString
add constraint fk_tblTweetBySearchString_tblSearchString foreign key (searchStringId) references tblSearchString(searchStringId)

go

/*

And here we have the actual results

I'm using a view so that a client application will not break if the table schemas change

*/
create view vwTweetsBySearchString
as
select  s.searchString, u.twitterUserName,t.tweet, t.tweetDate

from tblTweetBySearchString t_by_s

inner join tblSearchString s
on t_by_s.searchStringId = s.searchStringId

inner join tblTweet t
on t_by_s.tweetId = t.tweetId

inner join tblTwitterUser u
on t.twitterUserId = u.twitterUserId

Here is the proc we’ll use to move the data from staging to the tables we’ll actually query

use twitter
go

if object_id(N'uspLoadTwitterDataFromStaging') is not null
	drop procedure uspLoadTwitterDataFromStaging

go

create procedure uspLoadTwitterDataFromStaging
as
begin


	insert into tblTwitterUser (twitterUserName)
	select twitterUser
	from tblTwitterStaging
	except
	select twitterUserName
	from tblTwitterUser


	/*
	I could prime this table in a config step 
	but I want the whole thing to be driven by user configurable files in the end product
	or parameters on the command line

	Anyway, the PowerShell script is responsible for inserting the search string into the staging table
	*/

	insert into tblSearchString (searchString)
	select searchString
	from tblTwitterStaging
	except 
	select searchString
	from tblSearchString




	insert into tblTweet(tweet,tweetDate,twitterUserId)
	select distinct stg.tweet, convert(datetime,stg.twitterDate), u.twitterUserId
	from tblTwitterStaging stg
	inner join tblTwitterUSer u
	on stg.twitterUser = u.twitterUserName

	except select tweet,tweetDate,twitterUserId
	from tblTweet


	insert into tblTweetBySearchString(tweetId,searchStringId)
	select t.tweetId, s.searchStringId
	from tblTwitterStaging stg
	inner join tblTweet t
	on stg.tweet = t.tweet
	inner join tblSearchString s
	on stg.searchString = s.searchString
	except select tweetId,searchStringId
	from tblTweetBySearchString






end

And here is the PowerShell script to get the data into the data staging table. Like I said before there is a lot to be done but it pages through the data and puts the data into our staging table using sqlcmd.




for ($i=10; $i -ge 1; $i--)
{
	
	
	$i
	$req = "http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100&since_id=1&page=$i"
	
	([xml](new-object net.webclient).DownloadString($req)).feed.entry |
	%{

		#replace problematic characters in input
		$title = $_.title -replace ("`t","``t")
		$title = $title -replace ("'","''")
		$title = $title -replace ("`n","``n")
		$title = $title -replace ("`r","``r")
		$title = $title -replace ("`"","`"`"")
		$published = $_.published -replace ("`t"," ")
		$published = $published -replace ("'","''")
		$published = $published -replace ("`n","``n")
		$published = $published -replace ("`"","`"`"")
		
		
		

		
		$splitData = $title.split(":")
		$twitterUser = $splitData[0]
		
		$tweet = $title.substring($twitterUser.length,$title.length - $twitterUser.length)
		if ($tweet.length -ge 2)
		{$tweet = $tweet.substring(2,$tweet.length -2)}
		
		

		
		#turn input into sql statement
		if ($tweet.length -gt 0){
			$sql = "insert into twitter.dbo.tblTwitterStaging 
			(twitterUser,tweet, twitterDate,searchString) 
			select '$twitterUser','$tweet' ,'$published','%23sqlhelp'" 
		
			#insert into staging table
			sqlcmd -E -Q $sql
		}

	} 
} 

sqlcmd -E -Q "exec twitter.dbo.uspLoadTwitterDataFromStaging"

And here is a view that our downstream systems will use to view the results

create view vwTweetsBySearchString
as
select  s.searchString, u.twitterUserName,t.tweet, t.tweetDate

from tblTweetBySearchString t_by_s

inner join tblSearchString s
on t_by_s.searchStringId = s.searchStringId

inner join tblTweet t
on t_by_s.tweetId = t.tweetId

inner join tblTwitterUser u
on t.twitterUserId = u.twitterUserId

In the next installment in the series I’m hoping to build out the PowerShell script to accept multiple search strings. I may play with the T-SQL a bit as that is the sort of thing I never get tired of doing.

Thoughts? Feature requests?

Posted in PowerShell, SQL | Tagged , , , , , , , , , | 1 Comment

Who needs people?

Whoever isolates himself seeks his own desire;
he breaks out against all sound judgment. Proverbs 18:1

Last week I wrote a bit about parenthood. I’ve been dwelling a lot on fatherhood and that’s forced me to take a good look at some of my weaknesses. One trend I’ve noticed is that I tend to isolate myself. I think I can get along fine without other people, but the truth is that I need people.

I think I first started to notice this at a past job. It was a tough job and I worked long hours but I couldn’t seem to get enough of it. But one by one valued co-workers left and as they left the job slowly became unbearable. The work was pretty much the same but it the team was falling apart. It was the team that drove me on to accomplish great things.

I eventually changed jobs and my enthusiasm was renewed but after a couple of years at that job once again my energy started to drain. It was really charged up back in November when I attended 2010 PASS Summit. Being around people who were excited about technology infused me with a new passion for what I was doing.

But it goes deeper than the job. I love my job but my job is not my life. I recently started attending a weekly meeting of men who are trying to be open about our problems and struggles. It was in listening to the stories they told – burdens they no longer feel able to carry, dreams that have been shattered, addictions they’ve been keeping secret – that I was able to first articulate the pains I’ve had that have driven me to do some pretty awful things: lies, betrayals, etc.

At heart I believe people are primarily creatures who need relationships. There is something in us – planted by God – that needs to connect.

My tendency is to retreat but I see now that I need to start investing in relationships. I know this will help my career but I’m hoping it will help me in all areas of life.

I’d like to leave you with a challenge. Can you list in the various areas of your life – work, personal, spiritual, etc – the people you can count on?

Posted in Business, Life, Parenthood, spirit | Tagged , , | Leave a comment

Downloading the results of a Twitter Search using PowerShell

I’m trying to learn PowerShell because I need to automate some things. I was a little skeptical about all the hype surrounding PowerShell. What could it possibly do the Perl didn’t? But I knew it integrated really well with .NET and after reading Andy Warren’s post I figured PowerShell made sense. Even Paul Graham thinks for automation or glue programs you want to use the language that has the libraries you need and not LISP. When Paul Graham says “don’t use LISP” you have to listen, right?

It turns out PowerShell is pretty fantastic.

@Kendra_Little asked: “Is there a way to search the archives of #sqlhelp? Trying to remember answer to a question i know i saw a couple months ago.”

@anonythemouse suggested she use PowerShell to search Twitter and archive the results.

I was just beginning to get interested in PowerShell so I decided to see if I could do this. I figured learning how to do this would help me learn PowerShell even if this particular script didn’t have much of a shelf life. But it might end up being very useful in itself. There might be other searches that I’d want to archive or I might decide that I don’t like some aspect of the GUI to whatever tool I’m using to interact with Twitter or I might want to pipe Twitter data into some application I’m writing.

Well, how easy is it to get Twitter search data using PowerShell? Actually, it’s incredibly easy.

I lifted this code from Windows PowerShell in Action
by Bruce Payette.

([xml](new-object net.webclient).DownloadString(
"http://blogs.msdn.com/powershell/rss.aspx"
)).rss.channel.item | format-table title,link

I had to modify it slightly to switch from RSS to Atom and I removed the formatting because I want to play around with things a bit.

([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry

So that is how to get a search of Twitter into PowerShell almost no code. (Note, you can get the URL for the Atom feed of a Twitter search by searching here and then looking for “Feed for this query” link on the results page. Other options you might want to put in your URL are described here)

Anyway, we’re not quite where I want to be. I’d like to get rid of some of the meta-data I don’t want. So we’ll step through the collection. We can use the ForEach-Object to do this. I’m going to use an alias of the ForEach-Object – %. It’s just a quick way of writing ForEach-Object. And I’ll be using a special variable $_. $_ basically refers to the current object.

([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry |
%{
	$_.title
	$_.published
	"

	"
}

I’m adding a | which means direct the output of this command into the next command. The next command is the ForEach-Object alias and then we apply the code between the {} to each object in the Atom feed we got from Twitter. I then output the title attribute which is essentially the tweet and the published attribute which tells me when the tweet was made. (The blank lines between the double quotes are just to make the output easier to read.)

Now, to get it into my database I could invoke sqlcmd or export this data to a file. In the long run I think storing it in a database would be a good idea but I don’t want to muddy the waters with that just yet. Instead I’ll export it to a file.

$file = ([xml](new-object net.webclient).DownloadString(
"http://search.twitter.com/search.atom?q=%23sqlhelp&show_user=1&rpp=100"
)).feed.entry |
%{
	
	$title = $_.title -replace ("`t"," ")
	$published = $_.published -replace ("`t"," ")
	$title + "`t" + $published
	
} 

$file  | out-file -filepath sqlhelp_twitter.txt -encoding Ascii

There you have it. A tab-delimited file of a Twitter search. It only gets the last 100 Tweets but the Twitter Search API shows you how to page through the results.

I hope to expand on this example a bit and maybe do some more interesting things with the Twitter API and the Twitter Search API. But the point is that PowerShell lets you do some pretty neat stuff in a fairly small amount of code. Reminds me of my Perl days. In fact, PowerShell seems as exciting to me as when I was first learning Perl.

Posted in PowerShell | Tagged , , , , , | 2 Comments

T-SQL Tuesday # 13 – Asking the business good questions


Fatherhood involves more than just having dried food on your shirt. People expect you to do crazy things like wave your arms like a muppet or impart wisdom to the next generation. I don’t know that I have much wisdom but I have had a few moments of clarity that I hope I can pass on to little Zeke and any brothers or sisters he might someday have.

One of those moments of clarity came during Warren Thornthaite’s PASS Summit session on dimensional modelling. He said – and I’m paraphrasing here – don’t ask users what they want, ask them what they do.

It was in reflecting on this that I really latched on to the power of asking good questions. I had been doing this for a while but had not reflected on what it was I was doing.

Soon after starting my current job one of the BAs realized that I could be counted on to discover database problems quickly. He would often call or email vaguely defined issues like “a user says this number doesn’t seem right”. But usually I could get to the root of things quickly. Sometimes I would discover bugs. Sometimes I would discover that the number was accurate based on the inputs and the business rules. The number was just not what the user expected – or perhaps hoped.

After a certain time I spent more and ┬ámore of my time discovering things in the later category and decided that I needed to cut down on the number of false bug reports. I started asking questions of the BA. “How did the user discover the bug? What do they think the number should be and why?” And the BA quickly discovered that as he asked the users these questions many of the bugs disappeared.

There was nothing disrespectful in the way we asked these questions. We knew we could not trust that the user was always right. And we found that asking good questions was a great tool to help us cooperate with the users and help them better understand their data.

Asking good questions also seems to be helpful in all sorts of other situations, too. When I see two co-workers getting frustrated trying to communicate sometimes a good question helps alleviate the problems.

I’d like to say I’ve used this skill a lot in my personal life – with my family, as I try to grow spiritually, etc. But I have to admit this is an area I’m still working on.

I do sort of wonder why there are certain skills that come easily at work but not at home.

Anyway, what are some good questions you ask? What would you like people to ask you?

Posted in Business, Life, Parenthood | Tagged , , , , , | 3 Comments