Knowledge Blog

SQL 2005 Database Copy Error: The specified @subsystem_name ('SSIS')

dj@graphxsoftware.com (David Johnson) — Mon, 14 Nov 2005 00:45:51 GMT

Task: Use the 2005 "Copy Database" function to copy the SQL2000 databases to SQL2005. The ultimate goal is to copy all the live databases from SQL2000 to SQL2005, check everything has upgraded ok, then uninstall SQL2000.

Error #1: First option I tried was "Use the detach and attach method". Using Windows authentication. Selected the databases, tables and object to copy. Create package name. On the "Schedule the Package" screen only option avail is "Run immediately". The "Integration Services Proxy Account" option is greyed. Upon hitting "NEXT>" I get a popup "Proxy account has to be selected to proceed further." That's as far as I can get with this option.

Error #2: Second option I tried was "Use the SQL Management Object method". Using Windows authentication. Selected the databases, tables and object to copy. Create package name. "NEXT>". Immediately at the "Schedule the Package" screen a popup appears: "The specified @subsystem_name ('SSIS') does not exist. (Microsoft SQL Server, Error: 14262)".

Solution:
A common situation more and more are coming across getting aclimated to the *new* sql agent architecture.

you will need to install SQL integration services which may not be installed by default. (you can run the install package again and add it) the service is required to execute the package and the missing / reuired proxy account is created during the Integrtion services installation.

HTH
David Johnson
www.davidjohnsondeveloper.com

Google Map based GuestMaps

dj@graphxsoftware.com (David Johnson) — Sun, 06 Nov 2005 23:28:21 GMT

There are a lot of variations of this unique way to provide guest interaction to your web site, and recently I have had the need to explore some the google map api capabilities.

this GuestMap is the side effect of some of the research I did. I will compile some documentation to the solution but, all source code is only a proof of concept and provided as-is. feel free to extend it how ever you like just please provide a link back to this site in the source code.

I would be glad to help anyone make this work for their site.

the solution is developed in ASP.Net 1.1, javascript and HTML, leveraging a SQL back end.

I began to develop Flash Admin UI's to overlay the google map to provide configuration screens, but the additions were beyond the scope of my research.

If you would like some ideas on that feel free to ask.

source code download

check out a working demo

Tutorial will be available soon.

p.s. I am so excited. been working with the final Visual Studio Team System 2005 bits all weekend, nice speed improvements over June and August CTP's. more on that later!!

have a great day!

50 degree w/ rain here couldnt be better development weather!

Tutorial: Adding Conditional Formatting into Reporting Services

dj@graphxsoftware.com (David Johnson) — Tue, 25 Oct 2005 21:29:41 GMT

The task at hand...

1) alternate row colors in SQL Reporting Services

Tutorial 1 – Alternating Colors Between Rows

To make our report more readable, your viewers probably want to have each row an alternate color. To do this, pull open your report and follow these steps:

Select one of the fields in the report. Once you select the field, you’ll see a grid surrounding the row. Left-click on the grey column to the left of the data as shown in the below figure. By left-clicking on this row, you can now set the properties for the entire row and not a single column in the table.
The property you want to set in the right-pane is the BackgroundColor property. If you don’t see the Properties window on the right as seen in the above screenshot, select Properties Window under View. The default background color is Transparent. To change this to our dynamic property, click the drop-down box and select <Expression…>.
You should not be in the Edit Expression dialog box as shown in the below screenshot. In the Expression box to the right you can type the following syntax to alternate colors (make sure you remove what was there before):

=iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")

Essentially this variant of .NET code above means that the first row will be a slightly grey color and the second will be white. Then, the rows will alternate between the colors. If you’d like to change the colors, just type the new color name where WhiteSmoke is in my example. The list of color names can be found where you clicked the drop-down box to select <Expression…> earlier. If you wanted something to occur every 3^rd row, change the 2 in the above code to 3.

After you have typed in the expression, click OK and you’re done! Almost anything can be set dynamically like this in Reporting Services. For example, if you’d like the report to look different based on the UserID that generated the report, you can do that by using the global variable.

Tutorial 2 – Changing the Color of Text Dynamically

Next, we want to make bad numbers in our report jump out. To do this, we’re going to dynamically look at the data in the Job Status column and if it says Failed, turn it red so it stands out for a casual viewer. To do this, follow these simple steps in Visual Studio:

Left-click the row and column that you’d like use dynamic formatting on.
You will again want to go to the Properties Window and select the drop-down box under Color in the Appearance group. Select <Expression…> to open the Expression Editor.
Replace the word Black (which is the default) with the following text and click OK.

=iif(Fields!JobStatus.Value = "Failed", "Red", "Black")

Now, you can preview the data and see if can see any failed jobs. Essentially, the syntax above says that if the value of the JobStatus field is equal to the word Failed then turn the color to Red. Otherwise, keep it black. Likewise, you could change the color based on profits not being met with the following syntax.

=iif(Fields!Profit.Value < 0 , "Red", "Black")

You can also make the word Failed really stand out by making the font style a bit more dynamic. If you want the text bolded dynamically, then you can change the Normal default in the FontWeight property (under font) to the following syntax:

=iif(Fields!JobStatus.Value = "Failed", "Bold", " Normal ")

Well hopefully this showed you a bit about how to make two simple but necessary changes to your report to make them more readable. In the next articles in this tutorial series, we’ll begin securing our report that we’ve just created so you’ll need special rights to see the job status.