Comparing 2 PCs Inventory

I've decided to try another method to compare 2 PCs inventories via the
ADD Remove Displayname.
I have the following code I'm trying to use to compare the two and put
in the Netbios_Name0 column PTLSTANDARD if it is lacking that software
or the @.name2 computer name if it exceeds the standard. It works fine
when the Displaynames match (the @.name2 computer does not have the
Date/Time data in it) However, when the code finds that the reference
computer and the @.name2 don't match because of the Date/Time data it
displays it as both lacking and exceeding.
I'm racking my brain how to institute some form of LIKE where the
reference computer DISPLAYNAME0% is LIKE @.name2 then toss it out of the
mix.
Here is the code and the output.
Thanks Fred
Declare @.name1 varchar(20)
Declare @.name2 varchar(20)
Set @.name1='PTLSTANDARD' /* THE REFERENCE PC */
Set @.name2='PTLWCSG8F11LAB'
select
(case when inv1.Displayname0 is not null then @.name1 else @.name2 end)
as Netbios_Name0,
(case when inv1.Displayname0 is not null then inv1.Displayname0 else
inv2.Displayname0 end) as DisplayName0
from
(
select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
from v_GS_ADD_REMOVE_PROGRAMS s
join v_R_System sys on s.resourceID= sys.resourceID
where sys.Netbios_Name0 = @.name1
) as inv1
full outer join
(
select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
from v_GS_ADD_REMOVE_PROGRAMS s
join v_R_System sys on s.resourceID= sys.resourceID
where sys.Netbios_Name0 = @.name2
) as inv2
on inv1.Displayname0 = inv2.Displayname0
where (inv1.Displayname0 is NULL) or (inv2.Displayname0 is NULL)
Order by Netbios_Name0, Displayname0
Netbios_Name0 Displayname0
========================================
=============================
PTLSTANDARD Catalog for UnumProv_All_03.1
PTLSTANDARD Catalog for UnumProv_All_r054
PTLSTANDARD Client Application Enabler for UDB
PTLSTANDARD COMTI Client Common 2002.7
PTLSTANDARD Flash Player 6
PTLSTANDARD IBM CICS Universal Client
PTLSTANDARD Norton Location Pointer
PTLSTANDARD SNA Pointer
PTLSTANDARD Websphere Client 5.3
PTLSTANDARD XML Parser 3.0 SP2
PTLSTANDARD XML_Parser_4.0_SP1
PTLWCSG8F11LAB Catalog for UnumProv_All_03.1 5/19/2003 2:06:03 PM
PTLWCSG8F11LAB Catalog for UnumProv_All_r054 6/13/2005 11:01:08 AM
PTLWCSG8F11LAB Client Application Enabler for UDB 7.2 4/14/2003 8:23:46
AM
PTLWCSG8F11LAB COM CommAPI 2003.1 6/5/2003 4:19:54 PM
PTLWCSG8F11LAB COMTI Client Common 2002.7 11/14/2003 8:09:28 AM
PTLWCSG8F11LAB Flash Player 6 4/14/2003 8:37:35 AM
PTLWCSG8F11LAB IBM CICS Universal Client 4/14/2003 8:27:06 AM
PTLWCSG8F11LAB Norton Location Pointer 5/17/2005 4:13:00 PM
PTLWCSG8F11LAB SNA Pointer 4/14/2003 8:30:58 AM
PTLWCSG8F11LAB Websphere Client 5.3 4/14/2003 8:42:01 AM
PTLWCSG8F11LAB XML Parser 3.0 SP2 4/12/2003 3:34:58 AM
PTLWCSG8F11LAB XML_Parser_4.0_SP1 4/12/2003 3:47:23 AMHow about if you use a substring, so your comparison would be something like
this:
inv1.Displayname0 = substring(inv2.Displayname0, 1, len(inv1.Displayname0))
You can use the left function for this too which would be:
inv1.Displayname0 = left(inv2.Displayname0, len(inv1.Displayname0))
Sean
"FStuart" <fstuart1@.maine.rr.com> wrote in message
news:1122056014.704562.15960@.g44g2000cwa.googlegroups.com...
> I've decided to try another method to compare 2 PCs inventories via the
> ADD Remove Displayname.
> I have the following code I'm trying to use to compare the two and put
> in the Netbios_Name0 column PTLSTANDARD if it is lacking that software
> or the @.name2 computer name if it exceeds the standard. It works fine
> when the Displaynames match (the @.name2 computer does not have the
> Date/Time data in it) However, when the code finds that the reference
> computer and the @.name2 don't match because of the Date/Time data it
> displays it as both lacking and exceeding.
> I'm racking my brain how to institute some form of LIKE where the
> reference computer DISPLAYNAME0% is LIKE @.name2 then toss it out of the
> mix.
> Here is the code and the output.
> Thanks Fred
>
> Declare @.name1 varchar(20)
> Declare @.name2 varchar(20)
> Set @.name1='PTLSTANDARD' /* THE REFERENCE PC */
> Set @.name2='PTLWCSG8F11LAB'
> select
> (case when inv1.Displayname0 is not null then @.name1 else @.name2 end)
> as Netbios_Name0,
> (case when inv1.Displayname0 is not null then inv1.Displayname0 else
> inv2.Displayname0 end) as DisplayName0
> from
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @.name1
> ) as inv1
> full outer join
> (
> select s.Displayname0, Version0, Installdate0, ProdId0, Publisher0
> from v_GS_ADD_REMOVE_PROGRAMS s
> join v_R_System sys on s.resourceID= sys.resourceID
> where sys.Netbios_Name0 = @.name2
> ) as inv2
> on inv1.Displayname0 = inv2.Displayname0
> where (inv1.Displayname0 is NULL) or (inv2.Displayname0 is NULL)
> Order by Netbios_Name0, Displayname0
>
> Netbios_Name0 Displayname0
> ========================================
=============================
> PTLSTANDARD Catalog for UnumProv_All_03.1
> PTLSTANDARD Catalog for UnumProv_All_r054
> PTLSTANDARD Client Application Enabler for UDB
> PTLSTANDARD COMTI Client Common 2002.7
> PTLSTANDARD Flash Player 6
> PTLSTANDARD IBM CICS Universal Client
> PTLSTANDARD Norton Location Pointer
> PTLSTANDARD SNA Pointer
> PTLSTANDARD Websphere Client 5.3
> PTLSTANDARD XML Parser 3.0 SP2
> PTLSTANDARD XML_Parser_4.0_SP1
> PTLWCSG8F11LAB Catalog for UnumProv_All_03.1 5/19/2003 2:06:03 PM
> PTLWCSG8F11LAB Catalog for UnumProv_All_r054 6/13/2005 11:01:08 AM
> PTLWCSG8F11LAB Client Application Enabler for UDB 7.2 4/14/2003 8:23:46
> AM
> PTLWCSG8F11LAB COM CommAPI 2003.1 6/5/2003 4:19:54 PM
> PTLWCSG8F11LAB COMTI Client Common 2002.7 11/14/2003 8:09:28 AM
> PTLWCSG8F11LAB Flash Player 6 4/14/2003 8:37:35 AM
> PTLWCSG8F11LAB IBM CICS Universal Client 4/14/2003 8:27:06 AM
> PTLWCSG8F11LAB Norton Location Pointer 5/17/2005 4:13:00 PM
> PTLWCSG8F11LAB SNA Pointer 4/14/2003 8:30:58 AM
> PTLWCSG8F11LAB Websphere Client 5.3 4/14/2003 8:42:01 AM
> PTLWCSG8F11LAB XML Parser 3.0 SP2 4/12/2003 3:34:58 AM
> PTLWCSG8F11LAB XML_Parser_4.0_SP1 4/12/2003 3:47:23 AM
>

Compare Schemas...XSD

Hey all,

I am currently researching ways to compare databases via an XSD schema.
I wrote a small app that creates a dataset from a database and exports
that dataset to XSD. This gives me an XSD file with tables and
relationships representing the entire database.

At this point, I am trying to find ways to compare these schemas. Does
anybody know of a way to do this easily and to record differences if
there are any?

Also, any information on comparing databases using any method would be
greatly appreciated.

Thanks,

--
ShockShock (no@.way.com) writes:
> I am currently researching ways to compare databases via an XSD schema.
> I wrote a small app that creates a dataset from a database and exports
> that dataset to XSD. This gives me an XSD file with tables and
> relationships representing the entire database.
> At this point, I am trying to find ways to compare these schemas. Does
> anybody know of a way to do this easily and to record differences if
> there are any?
> Also, any information on comparing databases using any method would be
> greatly appreciated.

My knowledge on XSD is weak, so I skip that question. For comparing
databases in general, a common recommendation is Red Gate's SQL Compare,
although I have never used it myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Shock (no@.way.com) writes:
>>I am currently researching ways to compare databases via an XSD schema.
>> I wrote a small app that creates a dataset from a database and exports
>>that dataset to XSD. This gives me an XSD file with tables and
>>relationships representing the entire database.
>>
>>At this point, I am trying to find ways to compare these schemas. Does
>>anybody know of a way to do this easily and to record differences if
>>there are any?
>>
>>Also, any information on comparing databases using any method would be
>>greatly appreciated.
>
> My knowledge on XSD is weak, so I skip that question. For comparing
> databases in general, a common recommendation is Red Gate's SQL Compare,
> although I have never used it myself.

I actually tested their software and it was quite good, but we can't use
it because of their licensing. They even provide an API that can be
used in .NET, but their licensing methods are terrible...read the fine
print.

--
Shock|||I'd recommend you have a look at Innovartis DB Ghost at
http://www.innovartis.co.uk/

John McGrath
SQL Server DBA MCSE

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Company footer message on all reports

I've looked around, but can't find this anywhere. Does anyone know of a way to add a footer to all reports (including new ones being created via report builder)? So that when users create new reports, the global footer is there.

Maybe create a "Company Footer" as a subreport?

Granted you will still have to insert the subreport into each of your report footers, but any changes needing to be made to the footer will only need to be done once.

|||That is an option, but we want all users to be forced to use this.
|||

The best bet would be to create a report template and then have all report developers use the template.

To create a report template:

create a report with all the common elements, store the .rdl file in the Report Designer template directory (in a default installation this would be C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject|||This is an excellent suggestion, and one we will probably use for developers. However, the main area we want to catch is the Report Builder reports. Here we can't possibly train all the users to include a subreport, or something similar. What we need is a way to alter the predefined Report Builder template, to add a company footer. Any ideas?

Company Dimension

We deal with multiple vendors who provide us information via text/xml files. Vendor A may provide financial data, vendor b provides litigation data, vendor c provides ratings data. Our current structure has databases for each vendor with its own company table which basically makes all this data disconnected. Of course each vendor has its own proprietary company id to make records unique.

All of the data is based on companies so the grain of data would be at a company level. I would like to be able to link this information together by creating a dimensional model that has a single company table (DimCompany) and has facts populated based on the type of data we receive. Would this be the right sequence of events?

1. My initial load (historical) would have to look at all these data sources and create one company record in my DimCompany table. This table would then link to all other fact tables to provide a single view of company info. I would imagine this would have to be a fuzzy lookup since one company will be in all sources.

2. On subsequent loads (incremental) I would probably have to do a lookup of companies in the dimension via the proprietary code and add if the company wasn't there.

Any advice on tackling this issue would be greatly appreciated especially if SSIS was used in the process.

Hi jrp210,

Based on my interpretation of your schema, I would design DimCompany as you describe. One row per company, unique IDs, and you may wish to add surrogate keys - they will make your data mart / warehouse easier to scale.

I would also add a DimInformationType for the different types of data you receive.

Another option is to snowflake the InformationType table off of DimCompany. This is common when the referenced lookup table is relatively small and doesn't change often.

There are performance implications but in smaller data warehouses (~1 - 2G) you will probably never notice. And if you do, an index will likely clear up any performance issue.

I don't follow the need for a fuzzy lookup. These are expensive in SSIS and should only be used when necessary.

You are correct: incremental loading does require a lookup (or a merge join) to detect new records.

There's lots of good information online about how to do this. I suggest picking up one of the Kimball books for help in designing data warehouses - I particularly like the Data Warehoue Toolkit which is updated to include information on using SSIS for ETL.

Hope this helps,

Andy

|||

Here are my 2 cents:

Having a conformed 'Company' dimension is the way to 'join' all fact rows. Not sure about using a Fuzzy lookup as it may give you non-expected results; so evaluate yourself the margin of error your company can tolerate. In general you may want to asses the level of cleanness of your data, so you can anticipate the results

About how to organize the fact data; I would say that depends on the way the data is going to be used during analysis and reporting, and on the nature of the data. You may want to spend some time with the end users to get that feedback; draw a data model and review it with them again; this may take a few iterations.

Andy suggestion about Kimball’s book is also a good idea.

Good luck with that

|||

Thanks for the response. I did purchase the book you referenced. It has been very helpful but doesn't delve into the issue of trying to create one dimension table from multiple sources with different primary keys.

I will definitely be creating surrogate keys because more data sources will be introduced over the long haul.

I don't follow the DimInformationType table. Is this more of a helper table to map the surrogate company key to the proprietary key used by the vendor? For example:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

When its time to access vendor B's file, most of the companies will be in the DimCompany table. If not, follow the same procedure as above. If they are in the DimCompany table then I will have to add a row to the vendor b helper table with the surrogate key/vendor b key.

It all stems from the fact that each vendor has its own proprietary (different) key for the same company. I don't know how I would get around not using fuzzy logic or some sort of text mapping. Text mapping could be dangerous as well since the names may be slightly different.

|||

There are 2 other books from Kimball’s group:

The Data Warehouse ETL tool kit

The Microsoft Data Warehouse Toolkit

The first one cover how to conform dimension (coming from different sources).; the second one covers Kimball's warehouse methodology using SQL Server 2005 tools.

|||

jrp210 wrote:

The first time I run the historical load I will have to insert companies (take Microsoft for example) into my DimCompany table. This will be done by using vendor A's company info, vendor B, and so on. Microsoft's companyId in vendor A's system might be 123456, in vendor B's system it may be 789101. I want one instance of Microsoft in my DimCompany table so I will have to do a lookup to make sure it isn't already in the table before inserting. If it is not in the DimCompany table then I will add to DimCompany and then add to another table that has the surrogate key/vendor a key combination.

Hi jrp210,

That's different from what I understood previously. Don't feel bad, this happens to me a lot.

Maybe your schema looks like this:

CompanySK (surrogate key) CompanyName (business key)
1 Microsoft

VendorSK (surrogate key) VendorName (business key)
1 Vendor A
2 Vendor B

VendorCompanySK VendorCompany_CompanySK VendorCompany_VendorSK VendorCompany_ID
1 1 1 123456
2 1 2 789101

This is a snowflake that allows you to utilize Company and Vendor separately in facts, and also use VendorCompany in facts. There are foreign key relationships between DimCompany and DimVendorCompany, and DimVendor and DimVendorCompany.

Hope this helps,

Andy

|||

Andy,

My goal would be to create one company record from multiple sources. I don't necessarily need a vendor or vendorcompany table but will probably need their proprietary company id as an attribute in my company dimension table so that one could link back using their key.

I would assume something like this:

CompanySK
VendorA CompanyId
VendorB CompanyId
VendorC CompanyId
Company Name

For the most part one row should contain an Id for vendor a,b, and c but there are times when that is not the case. Is that why the company name would be the business/natural key?

|||

The first thing you need to do if to define the grain of your dimension; it looks to me like the grain is one row for each company (even when that company exists in several vendor data sources); so if you have more than one 'version' of a company, like in your example of Microsoft company, you would need some kind of auxiliary table to keep that 1:many relationship between the many rows/company in the source and the 1 row per company n your dimension.

BTW, this has nothing to do with SSIS...but I hope it helps

|||

You are right about the SSIS - probably more geared toward dimensional modeling/DW. But, I am using SSIS to do that so this is where I originally posted to.

Yes, grain of the dimension is the company. The really isn't more than one version of the company. It is the same company coming from different source (vendor) systems. The vendor systems have different unique keys in which they tag a company. Because of this there isn't one natural key to use across all. If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

|||

jrp210 wrote:

If I understand what you are saying is that this auxillary table would "create" the natural key that will be used in the dimension table?

That is right. You could create a surrogate key in the dimension and then that auxiliary table will keep the relationship between source system keys(many for a company) and the Dimension surrogate key (one per company).

|||

At first it makes sense but to initially load the database how would you keep the company unique in the DimCompany table without a key that would link them together? Or better yet put, which comes first loading the auxillary table or Dimension table?

I was assuming the auxillary table would be loaded first:
VendorId
VendorCompanyCode
CompanyName
etc.

But then I would have to create a unique key that would be used in the DimCompany table. There would have to be another table that then creates this key (identity column) that would be used in the DimCompany table.

Compacting a db via T-SQL

Hello dbForumers,

I was wondering if there is a way (MUST be) to instruct SQL server to compact a chosen database's files. I have a batch that runs every night who generate a huge amount to log lines that I get rid of with a backup log xxx with truncate_only, but still the logfile is several GB big afterwards with a lotsa empty space... I wanna get a clean small file everyday =)

Thank you!Its not ideal to shrink the Transaction log every day, rather define a set of size by testing the activity on the database.

During that batch overnight take before and after sizes for Tlog and set the higher level.

Also maintain regular backups of Tlogs which will reduce the size of logical file and helps to fillup the Tlog quickly. If RECOVERY MODEL Is set to SIMPLE then ensure full database backups are carried in regular intervals.|||try DBCC SHRINKFILE

USE UserDB
GO
DBCC SHRINKFILE (logical file name,Target_size)
GO

Target_size is how much free size is left over after you shrink
if you leave this off, you will get the default.

also
you may have to switch the VLogs internally before you can shrink to a size that you desire...
all of his should be done after a tlog backup.
look up "Shrinking the Transaction Log" AND DBCC Shrinkfile in [BOL]

Communication Link failure

Hello,
Sometimes my application accessing SQL Server via ODBC is giving an error message "Communication link failure".
The network and server looks fine, but I'am still getting it time to time.
Please recommend possible reasons and solutions.
Thanks indvance.We has some unnoticed unstable connections very difficult to find because NT retried to connect without warning (maybe just some lattency when you tried to access again a lan resource).
We checked with "ping -w5000 -t"
The problem was bad coupling male/female RJ45 connectors in some stations.

Hope this helps,
Csar.

commandtimeout on privat adapter

Hi,
The adapter in an dataset, via generted code in VS2005, is defined private.
How can I set the commantimeout in this case.
For example, this a snipped from generated code on a dataset:
Public Overloads Overridable Function Fill(ByVal dataTable As
dsDeltaV.INTERFACEDataTable) As Integer
Me.Adapter.SelectCommand = Me.CommandCollection(0)
'following line is added manualy, but ofcourse overwritten next time the
generation tool has run:
Me.Adapter.SelectCommand.CommandTimeout = CommandTimeOut
If (Me.ClearBeforeFill = true) Then
dataTable.Clear
End If
Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
Return returnValue
End Function
Hello Bart,
I would like to suggest you add a custom partial class into the project
which is in the same namespace and has same name of your adapter class.
In that partial class, you could add your custom method to refer the
SelectCommand and other protected variable.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.