SHARE
TAKING TOO LONG?
CLICK/TAP HERE TO CLOSE LOADING SCREEN.
Up, Up, and Away!
CLOSE SEARCH

Execution time not visible in SSRS Excel export

I was struggling for a bit in trying to figure out why my execution time was not visible in my SSRS Excel export. The issue arose when I was trying to print out an SSRS generated chart, which its footer contained the page numbers, along with the report’s last generated date and time. As you can see when I tried to print the report, the only thing that was showing for the last generated date and time was the static text ‘Report Generated’

Report generated missing date/time.

Report generated missing date/time.

Well, when I tried to export the report to PDF, and Word, both print layouts displayed the date and time. So what gives? Upon inspecting the global variable [&ExecutionTime] variable used, I noticed that the markup type was set to ‘None’

Maekup type set to 'None'

Maekup type set to ‘None’

Clicking the drop down for ‘MarkupType’ I was given an option to set it to ‘HTML’. HTML? Odd, I thought. Oh well, let’s do it.

Markup type set to 'HTML'

Markup type set to ‘HTML’

What do you know? By setting the markup type of [&ExecutionTime] to ‘HTML’ it successfully output the date and time the report was generated.

Report generated with date and time.

Report generated with date and time.

It’s one of those conundrums, that I was able to resolve by fiddling with the Report Builder. I later confirmed it did work for other exports as well (PDF and Word). Not sure what exactly ‘HTML’ means in the sense of the report exported to Excel, which is a client app. The more and more I work with SSRS, the more I am discovering.

Here are some resources, from Pluralsight, that can help aid you with Reporting Services (SSRS)



Business Intelligence with SQL Server Reporting Services


List Price: $49.99 USD
New From: $34.83 USD In Stock
Used from: $43.50 USD In Stock

Professional Microsoft SQL Server 2012 Reporting Services


List Price: $49.99 USD
New From: $25.93 USD In Stock
Used from: $26.00 USD In Stock

Microsoft SQL Server 2012 Reporting Services (Developer Reference)


List Price: $61.99 USD
New From: $37.77 USD In Stock
Used from: $34.30 USD In Stock

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Source Type 200 in SQL Server Import and Export Wizard

Recently, I received the pain in my butt ‘Source Type 200 in SQL Server Import and Export Wizard’ error. I have been working on a project that requires me to pull a dataset from our testing SQL server instance to our development SQL server instance. Originally, I thought this would not be a big deal. Little did I know that this would be a big pain in my ass. So, what I needed to do was use SQL Server Management Studio’s Import and Export Wizard. With this tool I would import data from our testing server into our development server. You can access this wizard by right clicking your database, selecting Tasks, and clicking Import Data.

SQLImportExportWizard-2015-01-14_11-44-59

So, as you get into the wizard, it will ask you what the source and destination for your dataset will be. The source was our testing SQL server instance and the destination was the development SQL server instance. The next step was to determine how you would import that dataset. Were you going to import that data from an existing table or view? Was it going to be imported by a custom query? In my case, I would be importing my dataset from a custom query I wrote.

TableQuery-2015-01-14_11-52-25

On the next part of the wizard I provided my custom query.

SQLQuery-2015-01-14_11-08-36

Well, the next was was to edit the mappings, so that the source and destination columns and data types would match.

ColumnMappings-2015-01-14_11-09-00

So for each column I corrected it’s datatype. After I corrected everything I continued with the wizard. The next thing was to review these data type mappings. As you can see there were 13 unknown column type conversion. I was pretty confused on this considering I just mapped the columns along with their corrected data types on the Column Mappings wizard. I even updated the query to cast each returned column’s to the correct data type.

ColumnMappingsError-2015-01-14_11-09-14

So the error was the source type being 200. I even seen the source type be 201. Who knows what other source type errors numbers there. After googling, stack overflowing I came across this help on stackoverflow. Apparently, you can update an XML file on your client machine. This XML file defines a source data type to destination data type mapping. When you see a 200 or 201 failure, you can correct this by digging into a few XML files. For transferring between SQL server instances, you need to look at the following XML files:

  • MSSQLToSSIS10.xml
  • MSSql9ToMSSql8.xml
  • MSSql10ToMSSql9.xml

which can be located at the following: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\MappingFiles\

The one that I updated was the MSSQLToSSIS10.xml file. Within it you will see various mappings ranging from smallint, int, real and floating numbers. What I did from that point was add the SourceDataType error number 200/201 to this XML file and it’s corresponding DestinationDataType mapping of DT_STR.

200Fix-2015-01-14_11-12-45

You needn’t restart SQL Server Management Studio, at least I did not. So, at this point fire up the SQL Server Import and Export Wizard again, go through the steps we talked about previously. Once you get to the Review DataType Mappings, you will see that all of the data type mappings succeeded. Whew! Glad that madness is over with.

ColumnMappingsSuccess-2015-01-14_11-09-14

Finally, you can finish the wizard and have your dataset imported from your source’s database table into your targeted database’s table.

CompleteWizard-2015-01-14_11-18-03

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Find last modified table, stored procedure, or view in SQL Server

Just recently, I was in the need to find last modified table, stored procedure, or view in SQL Server that were related to this task I have done for a project I am working on. Reason is, is that I am sending all of these new scripts to our DBA so he can deploy to our testing server. Sometimes, if I forget what I was working on SQL Server wise. I’d like to know what scripts I created or modified in the last number of x days. Well, this isn’t all that hard to do. What you need to do is run some queries on views that reside on your master database. You can find these views at the following location:

Object Explorer>Databases>System Databases>Master>Views>sys.sysobjects

MasterViews-2015-01-19_16-30-56

Within these views there are a few important ones you need to be aware of when querying. In my case I was concerned about what tables, stored procedures and view were created in the last 19 days. The views that I need to query would be:

  • sys.objects (the mother load of eveyrthing)
  • sys.tables
  • sys.procedures
  • sys.views

I will show you how to query the mother load of all objects, sys.objects.

This will allow you to find any created or modified object in the past 19 days. Pretty easy eh? Will the same applies for the following tables, stored procedures, and views if you want to be more specific.

To query tables, stored procedures, and views you would use the following:

Lastly, you can query the sys.objects to be more specific. To do this, you would query the sys.objects along with its type. The types used in the next example will be:

  • U = Table (user-defined)
  • P = SQL Stored Procedure
  • V = View

If you need to query by different types below is a list of types:

  • Object type:
  • AF = Aggregate function (CLR)
  • C = CHECK constraint
  • D = DEFAULT (constraint or stand-alone)
  • F = FOREIGN KEY constraint
  • FN = SQL scalar function
  • FS = Assembly (CLR) scalar-function
  • FT = Assembly (CLR) table-valued function
  • IF = SQL inline table-valued function
  • IT = Internal table
  • P = SQL Stored Procedure
  • PC = Assembly (CLR) stored-procedure
  • PG = Plan guide
  • PK = PRIMARY KEY constraint
  • R = Rule (old-style, stand-alone)
  • RF = Replication-filter-procedure
  • S = System base table
  • SN = Synonym
  • SO = Sequence object
  • Applies to: SQL Server 2012 through SQL Server 2014.
  • SQ = Service queue
  • TA = Assembly (CLR) DML trigger
  • TF = SQL table-valued-function
  • TR = SQL DML trigger
  • TT = Table type
  • U = Table (user-defined)
  • UQ = UNIQUE constraint
  • V = View
  • X = Extended stored procedure

If your really wanting to dig more at sys.objects you can learn more on MSDN.

I know this very helpful to me when wrapping up this task and sending  it of to the DBA and QA. Hopefully, it helps you as well.

 



Professional Microsoft SQL Server 2014 Administration


List Price: $54.99 USD
New From: $26.43 USD In Stock
Used from: $26.44 USD In Stock

SQL Server Internals: In-Memory OLTP: Inside the SQL Server 2014 Hekaton Engine


List Price: Price Not Listed
Kindle Edition: Check Amazon for Pricing Digital Only

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Using Pomodoro to make studying more interesting

Recently, while browsing StackExchange’s “Personal Productivity” board I came across a question on how to keep studying interesting “How to make studying interesting like movies?“. Well, I decided to take the plunge in answering my first question on StackExchange on using Pomodoro to make studying more interesting. The poster asked the following:

Why is everything more interesting then studying ? (Eating, drinking, watching a movie, looking around, sitting, standing, daydreaming, listening to songs) Even doing the boring most thing is more interesting then studying. The reasons I think are:

lack of patience (when I read, I suddenly wish to finish the paragraph)
It is in my mind that I HAVE TO study. This makes studying even worse.
How to overcome these issues?

Is there a quote which keeps me motivating?

The first thing I thought of was maybe he should try the “Pomodoro” technique. If you do not know what the Pomodoro technique, you can learn more here.

In a nutshell this is what the Pomodoro technique is:

  • Write a list of all things you need to accomplish for today
  • Guesstimate how many Pomodoro it will take to accomplish (be realistic)
    • 1 Pomodoro is equivalent to 25 minutes
  • Set your timer for 25 minutes and focus solely on your current task at hand. Let nothing distract you (not even email, IM’ing, Facebook, Twitter, or any other social network, just get your work done)
  • When your 25 minutes have expired, take a 5 minute break (keep back and relax for a bit)
  • After 4 Pomodoro (25 minutes of work time x 4), take a longer break (typically 15 minutes)
    • Now get on your social media, chat with your friends, catch up on the news
  • Lastly, rinse and repeat. Do it all over again till your completely done with your tasks.

When using this technique, I typically get around 85%-90% of productivity done in the day. I have meetings, lunch, other things at work that pull me from being productive that I cannot say “No” to. Its a great technique to implore. Along with this, you may consider using a app called “TimeSnapper“. TimeSnapper is a great product, it allows you to keep track of your time and measure your productivity level per day. I use TimeSnapper extensively along with the Pomodoro technique. If you think you have A.D.D. try “Ignore the Unicorn: Pomorodo for the A.D.D.“. Hope this helps you a bit. Works for me.



Pomodoro Technique Illustrated (Pragmatic Life)


List Price: $24.95 USD
New From: $13.45 USD In Stock
Used from: $11.47 USD In Stock

ExcelSteel Cook Pro #260 Tomato Kitchen Timer, Red


New From: $12.22 USD In Stock
Used from: Out of Stock

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Linq to SQL Server using LinqPad: Creating, updating, and deleting operations

In this post I will show you how to use Linq to SQL Server using LinqPad, performing the following CUD (creating, updating, and deleting) operations.

Normally when I update our database I tend to use SQL Server Management Studio and execute SQL queries. Well, I have been on this kick lately to improve other skills, namely LINQ. As a person who doesn’t like to waste money, I had bought LinqPad was determined to use more LINQ queries than SQL for my day to day querying needs. Querying, no problem. CRUD operations… well I never have done that just yet with LinqPad. It’s never too late to start, you know.

So, first things first. Whenever you work with Linq you have a datacontext, in LinqPad when you first add your connection (your database), you go through the steps of setting your datacontext.

add-connection-2015-01-09_11-32-32

When you setup your datacontext you can be very specific on how you want your context setup. As you can see from the screenshots below, you can have LinqPad setup your context automatically, or you can build a typed data context built from your own assembly. I generally have LinqPad build it for me automatically. I am not that leet to do anything fancy.

datacontext-2015-01-09_11-29-44

On the next screen, you can set the type of provider you want. I use SQL Server in most cases, but if your a cloud buff you can choose SQL Azure. Other data you will need to provide is your server name, log on details, and the database type. I choose ‘Display all in a TreeView’. I leave the Data Context Options to default with pluralizing the entityset and table properties. and I include everything such as the stored procedures and functions. You can limit context to what you want, but I want everything.

datacontext-2015-01-09_11-30-21

Once your datacontext is setup, we can dive into the Linq part. One thing I switch from a default query is the Language. By default, it is set to C# Expressions. I switch this to C# Statements. What’s the difference between the two type of languages? Well from my understanding, with C# Expressions you can write simple queries that are one-liners. Well, I am sure you can do more than a one-liner with it, but I do simple queries using Lambda expressions or Linq queries.

With C# statements, I am allowed to use variables, multi-statements, different forms of control flow, and other things that your normally do with programming in C#.

langauge-types-2015-01-09_11-57-59

OK, enough priming, let’s dig in simple CRUD statements and get you going. So, I created a new connection to the AdventureWorks database. I should have done that in the beginning, guess I had a blonde moment.

aw2015-01-09_12-03-56

Anyways, I will  be working mainly with the Person table to do our CRUD operations. Below you will find the CRUD operations. It is pretty simple in LinqPad. Only thing you do not really have to do in LinqPad aside from normal projects using Linq is to declare the datacontext in code. It’s automatically assumed when you use the pluralized table name.

Lastly, you will  notice I used a Dump  function on the select statements. The Dump statement is basically allowing you to output the results of the query. One thing to note about the Dump statement, you can add a header to the output as such .Dump(“Select statement of the Persons table”).

dump-2015-01-09_12-27-10

So, what are you waiting for? Get LinqPad and get busy!



LINQ in Action


List Price: $44.99 USD
New From: $19.17 USD In Stock
Used from: $6.99 USD In Stock

Thinking in LINQ: Harnessing the Power of Functional Programming in .NET Applications


List Price: $39.99 USD
New From: $27.87 USD In Stock
Used from: $29.48 USD In Stock

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

How do I skip an iteration of a foreach loop using C Sharp

How do I skip an iteration of a foreach loop using C Sharp

How do I skip an iteration of a foreach loop using C Sharp

A question that came to mind was, how do I skip an iteration of a foreach loop using C Sharp? I am currently working on a project that deals with a lot of XML requests and responses. In a method that I am working on, I have a foreach loop that that sends out reporting requests for items I need using a solution provider ID. Sometimes those items belong to a different provider ID. For the provider ID, I put them in a dictionary that is iterated by the foreach loop. As I read the response, it may tell me that the item I need reporting for belongs to another solution provider ID. So, as the XML response is read, if the return status code indicates to me that it belongs to another solution provider ID, I break the current iteration of the foreach loop by using the continue command. The continue statement is used to pass control from the current iteration of the foreach loop, or other loops such as the while, do and for loops, to the next iteration in which it is enclosed. Initially, I thought I could use the break statement, but the break statement completely terminates the closest enclosing loop. Break statements in my experience was mainly used in switch statements, but I tried to apply it to the foreach loop, but alas, that did not work. So, a refresher on the differences between the break and continue statement, the break statement exits the closest enclosing loop completely, while the continue statement skips the current iteration of the closest enclosing loop to the next iteration.

 

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Evernote saved my career, and here’s how

Evernote saved my career, well for now it did. I am not the sharpest tool in the shed, however, I am the most hard working tool in the shed. With that said, Evernote, is really a helpful tool. A little background first, at my job, I work with a lot of brilliant individuals, so brilliant, I think they forget they are talking Aramaic to me. When they try to explain things to me, at their intelligence level and experience, sometimes it feels like the 1 Ceres asteroid smashing into my weak feeble body. So much technical lingo, so many variables, scenarios, and systems that interact with one another. In my line of business, I am fairly new to it. I work with remote monitoring, dealing with remote devices receiving and sending packets of data to and from satellites. All this is new to me. Very interesting stuff, yet hard to comprehend. For the past few months, I have been using Evernote to help close my understanding gap. Normally, when things are trying to be explained to me, it takes me a bit of time to process it. I have to sit down, and really think about what is being said. But in this industry, there is no time. Having to deal with scrums on a consistent basis, explaining where we are, what road blocks we encountered (if any), and what we will be working on next, and trying to process what your colleagues are going through is just so much information for one to process.

My memory sucks, it really does. I barely can remember where I put my car keys, nevertheless how our systems interact. I am currently reading a book that I am hoping will help with my memory, “Moonwalking with Einstein: The Art and Science of Remembering Everything” by Joshua Foer. Till I find out new techniques to increase my ability to remember things, I have been using Evernote on my Windows Phone. The one feature I use religiously is the audio recording feature. Every meeting that I attend, I record it. Every bit of detail of that discussion is archived for my use. Typically after meetings, I sit down and listen to what was discussed. This helps further to my comprehension of the topic our meeting was about. As well, it removes all doubt I would have if I didn’t do so. “Now what satellite were we supposed to receive packets from again?…”, “Was that a GSM unit or a Satellite unit?…”. That doubt and confusion is now removed from my mind. Just this past weekend, I was processing some meetings we had about this newer project I am supposed to work on. Man o’ man, what a relief to have this information by my side. Come Monday, I knew the right questions to ask, and my path to the project was made clearer. I highly recommend you use Evernote, or some form of data capture app. It will help you rest easier at night and have concrete info for what is to come with  your current task.

Evernote for Windows Phone: https://evernote.com/evernote/guide/windows_phone/

 



Master Evernote: The Unofficial Guide to Organizing Your Life with Evernote (Plus 75 Ideas for Getting Started)


List Price: $7.99 USD
New From: $3.58 USD In Stock
Used from: $2.99 USD In Stock

Evernote (75 Ways to Use Evernote to Supercharge Your Life)


List Price: Price Not Listed
Kindle Edition: Check Amazon for Pricing Digital Only

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Force a ‘Get Latest Version’ in Team Foundation Server

I needed a locally over written latest version of my solution. The way to go about this, is to force a ‘Get Latest Version’ in Team Foundation Server. Go to ‘Source Control Explorer’, find the folder that you want to force get, right click it and select Advanced->Get Specific Version…

Force Get Latest Version

Force Get Latest Version

Once you select that, a new dialog will show with the folder you selected. Below that, check both check-marks that are for ‘Overwrite writeable files that are not checked out’ and ‘Overwrite all files even if the local version matches the specified version’. Finally, click ‘Get’ and wahla you have gotten the latest files from your ‘Team Foundation Server.

Force Get Latest Version

Force Get Latest Version



Professional Team Foundation Server 2013 (Wrox Programmer to Programmer)


List Price: $59.99 USD
New From: $30.11 USD In Stock
Used from: $30.99 USD In Stock

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

Trace stored procedure using SQL Server Profiler

I came across this need yesterday when working on our project. I was needing to trace a stored procedure using SQL Server Profiler. Everything else, but this particular stored procedure, was just noise. So, in order to do this fire up SQL Server Profiler, tab over to the ‘Event Selection’ tab for the ‘Trace Properties’, set ‘Security Audit’, ‘TSQL’ events to unchecked, and leave ‘Stored Procedures – RPC:Completed’ to checked.

Trace Properties

Trace Properties

Next thing you would want to do is to customize the column filters. So, click the ‘Column Filters’ button on the lower right side of the ‘Trace Properties’ window. Once the dialog pops up, this is where you can edit any filter you want. For my needs, I needed to adjust the ‘Application Name’, ‘LoginName’, and ‘TextData’ filters. By default, the ‘Application Name’ is set for you.

Filter Application Name

Filter Application Name

I like to filter to only my application’s username. Since, we have numerous apps running hitting the same database instance I have to filter by username. If not, I’d get even more noise when running the profiler. So, I filtered by ‘LoginName’ as so.

Filter Login Name

Filter Login Name

And lastly, the granddaddy of them all, we finally filter by the stored procedures name. In this case we use the ‘TextData’ filter. I use SQL like query using the percent % wild card notation. It works like normal T-SQL. I am filtering anything before and after my filter for a match. I ran a few queries to see if it match each time, and to no surprise it did.

Filter Text Data

Filter Text Data

Here is the result list of the trace by SQL Server Profiler.

SQL Server Profiler Results Set

SQL Server Profiler Results Set

For the record, I hate SQL. So, anything I can learn to ease my use of SQL is great. What is your favorite debugging feature?

 

 

 

 



Mastering SQL Server Profiler


List Price: $29.99 USD
New From: $16.62 USD In Stock
Used from: $10.69 USD In Stock

Microsoft SQL Server 2014 Query Tuning & Optimization


List Price: $50.00 USD
New From: $27.16 USD In Stock
Used from: $25.99 USD In Stock

Microsoft SQL Server 2012 Performance Tuning Cookbook


List Price: $59.99 USD
New From: $48.00 USD In Stock
Used from: $38.05 USD In Stock

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal

View data in table variables using SQL Server Management Studio’s debugger

While working on this current project’s stored procedure, it did a lot of inserts into a table variable called @results. While running the debugger on the stored procedure, I notice when inserts were performed on the table variable I could not look at the data that was inserted into the variable. I tried looking through the Locals window, but the only thing that was shown was the variable name, its value, and type. Yet, the value that it was displaying was not what I was looking for. I was wanting an actual dataset. It only showed the value as (table)

Local window in SQL Server Management Studio

Local window in SQL Server Management Studio

There was no way to view the value. As you can see from above, the other variables had an inspector to view their values. However, in regards to this table variable it did not. I then tried using the Immediate window to run a SELECT * command. Nope, that did not work either. The result was that the SELECT * command cannot be evaluated. SQL debugger is so pointless at times.

Immediate window in SQL Server Management Studio

Immediate window in SQL Server Management Studio

After mucking around, trying to figure this out. I came across a StackOverflow post and saw someone else had this same issue. To view a table’s data set you would SELECT * everything while converting it to an XML document. You can then store the results into an XML data type, which then allows you to use SQL’s inspector to view its dataset.

Sorry about all the blurring of the result set. I cannot show that for business purposes, but you get the idea. Have a good one.

 

 

Hi, my name is David. I am a .Net, Windows Phone, Windows 8 developer. I created @gogetter for Windows Phone http://gogetterapp.com

about newsletter

Support my gambling addiction.

Donate via Paypal
VIEW PROFILE