BOL Package example Errors

Hi There

Ok i realize this may not be exactly the correct place to post this, but i seem to get good advice here.

I am trying to follow the SMO Tables DBCC Package Sample in BOL.

I have copied the Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.SmoEnum.dll to my latest .NET Framework folder as specified in BOL example.

Problem is when i try to run the package i get the following error:

An error occured while compiling the script for the Script Task

Error 30466: Namespace or type specified in the Imports 'Microsoft.SqlServer.Management.Common' cannot be found. Make sure the name space or the type is defined and it doesn't contain other aliases.
Line 9 Column 9 through 45

Imports Microsoft.SqlServer.Management.Common

Now i am guessing it has something to do with the SqlSmo dll's , i am not sure what to check as i have copied them to my .NET framework.

Someone suggested i should go to my .NET Framework 2.0 configuration and confirm that it has picked up the new smo dll's, but since installing the Beta .Net Framework 2.0 from the June CTP i get the following error when i try open the configuration:

Snap-in failed in to initialize
.NET Framework 2.0 Configuration

I have tried reinstalling the .Net Framework and i still get the same error, have not found any appropriate solutions on the net.

Are these issues related ? Any help would be greatly appreaciated.

Thanx
I'm not familiar with that sample. Where are you trying to use the SMO assemblies? In the script task?|||Hi Kirk

That is correct.

If you have installed the sample packages in the default directory.
You can open the project from the following path:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\SmoTablesDBCC\SmoTablesDBCC\SmoTablesDBCC.dtproj

If you have not installed tthe sample packages i can copy the script and post it here if you like.

Thanx|||Sounds like you haven't moved over the assembly.
I describe how to do that here:
http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Thanks,|||Hi Kirk

Ok please bear with me but i have a few questions.

Firstly here are the assembly imports as they are in the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Common ***
Imports Microsoft.SqlServer.Management.Smo

The one marked *** is the one with the compilation error.
Now for the example package i copied the Microsoft.SqlServer.SmoEnum.dll and Microsoft.SqlServer.Smo.dll assemblies to %windir%\Microsoft.net\framework\v2.0.xxxxx\ as specified. Now these refer to the import following the one giving me the error.So question number 1 what is the correct assembly for Imports Microsoft.SqlServer.Management.Common? As i can find no Management.Common assembly on the CTP assemblies folder? In other words i am not sure which assembly goes with that import.
Secondly I cannot find the option Project - Add References in BI studio from the project menu or an option like that in the script task editor, i have feeling i am being pretty stupid here, but i cannot, maybe it was another CTP release and it has another name in the June release?

Thanx Again Kirk|||Apologies, I assumed that the assemblies were correctly listed. The correct assembly for that namespace is actually: Microsoft.SQLServer.ConnectionInfo.dll
HTH|||Hi Kirk

Thank You it works now, i wonder why there is no mention of that assemly in the package examples in BOL? As it does nto work without it.
Lastly if you dont mind why did i not have to add a reference to it in BI, as stipulated in your link? As i mentioned above i cannot find this option in BI stdio?

Thanx Again|||Sean,
The documentation team are encouraging people to use the "Send Feedback" link in BOL if you've any problems with it. Drop them a line on that and it'll get actioned.

-Jamie|||

Lastly if you dont mind why did i not have to add a reference to it in BI?
Not clear on the question but will venture an answer.
You did add it in the VSA environment, right? The VSA environment is a sub-environment that is agnostic to and ignorant of the fact that it's running inside the BI environment. So, the VSA script can reference the assembly without the BI environment even knowing about it. I think that's the answer to the question...

|||

The information in the Readme for this sample explains some of the issues discussed in this thread.

The Script task project already contains the necessary References for all the managed namespaces that are also imported by using Imports statements. However certain DLLs need to be copied to the .NET directory as described in the Readme to be "visible" to the Script task.

You need to make the Project Explorer window visible to view and add References.

After copying the DLLs, you may need to close and reopen the script for the blue squigglies under the imported namespaces to disappear. If that doesn't work, try setting Precompile to False temporarily.

-Doug

|||Hi Kirk

That does clear things up, i found it in the VSA environment, bit unclear in the link i was trying to find it in BI, Thanxsql

Bits of Indexes RFC

I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.

My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.

Am I wrong in my conclusion?Nope...

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO

--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO|||WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!

Thanks Brett... Keep the info coming..|||...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.|||I get Index Scans...I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..

Hell the last 2 indexes are the same (if not larger) in...

But why would a 3 selects use each new index as I add them?

USE Northwind
GO

CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
GO

SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'

DROP TABLE myTable99
GO|||Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...

So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)|||So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.

It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..

In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)

In a table..no...in an index, it's imperative...at least that's what I've always believed...

For example...an index with Col3, Col2, Col1

And a predeicate of Col1 = something

Is a table scan...ever read up on index intersection?

Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5...got a very nice seek using IX1 thru IX3

Wonder why the optimizer chose them...

I'm (as usual, so it's not disconcerting) perplexed...