Solved

Invalid Object Error Using SQL-DMO Transfer2 object in WIndows XP Pro

Posted on 2004-08-17
13
617 Views
Last Modified: 2008-07-03
All of the latest updates are applied to my partitions at all times.  This is the code segment I am having trouble with:

 Dim objSQLServer As New SQLDMO.SQLServer2
 Dim objDatabase As SQLDMO.Database2
 Dim objTransfer As SQLDMO.Transfer2              

  With objSQLServer
   .LoginTimeout = 2
   .ODBCPrefix = False
   .ApplicationName = "Populate.Custom"
   .LoginSecure = True
   .Connect dBServer
  End With

  Set objTransfer = New SQLDMO.Transfer2

  With objTransfer
   .DestServer = dBServer
   .DestLogin = dBUser
   .DestPassword = dBPassWord
   .DestDatabase = SYSCONFIG_CustomerdB
   .DropDestObjectsFirst = True
   .CopyAllObjects = False
   .CopyAllStoredProcedures = True
   .CopyAllTables = True
   .CopyAllTriggers = True
   .CopyAllFunctions = True
   .CopyAllViews = False
   .IncludeUsers = True
   .IncludeDependencies = False
   .ScriptType = SQLDMOScript2_ExtendedProperty   'Include extended properties
   .CopyData = SQLDMOCopyData_Replace
  End With    

  Set objDatabase = New SQLDMO.Database2
  Set objDatabase = objSQLServer.Databases(dBTitration)
  objDatabase.Transfer objTransfer             'Do the transfer

This works perfectly in my 2K Server development environment.  I have created an Install Shield installation using Developer 7 which I can install and run without error in a 2K Pro non-development environment.  However, when I install it into a Windows XP non-development environment -- once the last line above is reached to actually do the transfer, an error is generated saying the transfer object is not an object.

There is also the minor issue of the fact that I am not able to use the SQL-DMO.User2 object in this procedure as well.  The .User object does work so I can live with that.  But it is an item of curiousity if anyone knows why it doesn't work here.
0
Comment
Question by:Peacemaker
  • 6
  • 6
13 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
Just a guess... does your development environment include the .Net Framework, and your non-development system not have that?
0
 

Author Comment

by:Peacemaker
Comment Utility
Thanks for your response jdlambert1.  The development environment has the .Net framwork 1.1 installed.  The 2K Pro non-development environment where the app does work, does not have the framwork installed.  The Windows XP partition has the framwork installed and is fully updated with the exception of the latest media player 9 SP.  My take on this is that the framwork is not a factor.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Sounds like the components didn't register right on the XP machine.  Have you checked to make sure all the SQLDMO dlls registered properly?  XP's system file protection is better than 2000 and some DLLs will not replace.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
This article talks about using the VB deployment wizard (crap), but the same applies, check your file locations and make sure everything is transferred and registered:

http://support.microsoft.com/default.aspx?scid=kb;en-us;258157

Brett
0
 

Author Comment

by:Peacemaker
Comment Utility
Thanks for your response, Arbert.  I'm using InstallShield Developer 7's MSDE 2000 merge module to gather the necessary files for the install.  There is no way to change things in those merge modules that I am aware of.  I do know that only SQLDMO.DLL is registerable in the list you referenced above, however.  After the installation has completed, I have gone to it and unregistered and registered it and tried again with exactly the same problem.  I've been fighting this for three days now.  It's really boring me to death.   I do believe it is something along this line but this doesn't make sense if you think about it.  Since when does an administrator not have full access?  It can't be something missing because I would have had a problem in the 2k Pro install in that case.  I would give up if that was an option, but it isn't.

I recompilled that above procedure to use Transfer object instead of Transfer2 object (omitting the Functions) to see if it was the object itself.  It is not, it is something in the object.   The error is always the same:

[SQL-DMO]Invalid parameter type; must be string or numeric ordinal, or an object; check documentation for this property or method.

It always occurs on the transfer method.  This same code and installation works anywhere except XP.
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
"Since when does an administrator not have full access?  It can't be something missing because I would have had a problem in the 2k Pro install in that case.  I would give up if that was an option, but it isn't. "

Sure there could be something missing or the wrong versions--like I said above XP system file protection is MUCH better and much more unforgiving than 2000.  Even if you're an administrator,  SFP will not let you cream the system DLLs.

What options do you have set for Installshield if it finds and existing DLL on the system?  Have you verified DLL versions between your 2000 server and the XP machine after an install?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Peacemaker
Comment Utility
Thanks again for your response, arbert.   What you're saying is not indicative of a lot of installshield experience using merge modules because you don't get the option to set anything with those.  You take them or leave them.  However, what you said did give me an idea that worked.  I unregistered SQLDMO.DLL and then went through the installed .rll's in the resource folder (PGF\MSS\80\tools\binn\resource) of the XP target machine and manually replaced them from the development partition.  Then I replaced all the SQLDMO related DLL's in the BINN directory.  Once I registered SQLDMO.DLL and restarted the Sql Server service, I tried the program again.  It worked perfectly.  As it turns out, the older files from my development machine (all dated 8/6/2000) worked but the newer files from the merge module dated 4/17/2001 did not work.  By the way, there was no existing Microsoft Sql Server (MSS) folder on the target machine so overwriting dlls was not an issue.  In the 28 years I have in the Micro computer industry, I think I have seen this situation maybe only one other time a really long time ago.  So, because you gave me the idea, you get the points and it looks like you really need them too.  :-)
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
28 years and you have experienced DLL/RLL hell?????

Sorry, I am familar with installshield and I know you can set the parms for how DLL overwrite happens--you're right though, that may not apply to merge modules...
0
 

Author Comment

by:Peacemaker
Comment Utility
It's really difficult to say all you mean in writing like this.  The matter of setting parms for DLLs in InstallShield is as you stated above in my experience, but my statement was regarding merge modules only which you also stated may be correct.  I really dislike having to deal with Installshield because I cannot devote sufficient time to it to learn all the intricacies as is necessary to be effective with such a package.  However, necessity is the mother of all invention.  Anyway, I’m going to pin down the specific .rll/.dll combination I need to resolve this current issue and then I’ll let you know what that combo was if you’re interested.  I feel it is pertinent to others because it is the latest MSDE 2000 Merge Module from InstallShield/Microsoft and the same thing is bound to happen sooner or later to someone else who didn’t read this thread.  :-)
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
You have to admit (I guess), it is easier than it use to be....The last installshield we deployed was a bunch of  C coding (or whatever the Installshield scripting most closely resembles) to handle the running of the msde install through a shell :)

Please do followup on your findings :)
0
 

Author Comment

by:Peacemaker
Comment Utility
In conclussion, I took the following way:
When using MSDE 2000 with InstallShield Developer 7 (sp4) there is a wizard that comes up when you select the MDSE 2000 merge module object.  It is quite simple and straight forward; unremarkable in itself except to say on the last page you should check the option for Sql Security if you intend to gain access to your server once it is installed.  Once this process finishes, there will be a good number of MSDE merge modules that have been checked for you depending on the options you previously selected.  Two of these options have "DMO" in them.  I unchecked both of these since the enclosed version of DMO does not properly support the transfer method.  Then I encluded two files from my development machine;  SQLDMO.DLL with a target of <ProgramFiles>\Microsoft Sql Server\80\tools\binn and SQLDMO.RLL with a target of <ProgramFiles>\Microsoft Sql Server\80\tools\binn\Resources\1033.  After the IS Project was built, distributed and place on a CD, the installation worked perfectly in the XP OS.  

This is a bit concering to me however.  I don't know the  internal structure of SQLDMO.DLL but out of all the files that are involved with it, only this one is registerable.  The .RLL files must be in a sub-folder specifically named Resource\1033 (if we're talking english) and the others are in the Binn and System32 folders.  Well, this configuration splits build versions.  That makes this approach very flimsy.  I wish there was a real solution to this problem.  But trying to move the hand of either Microsoft or InstallShield is not a small task.

Consequently, although this worked for this problem in this case, I do not recommend taking such an approach for solving problems.  Just because it worked does not mean it will consistently work under all uses employed by SQL-DMO.  

By the way, I tried replacing the entire set of SQL-DMO files and only keep what was needed for MDSE from the merge module.  InstallShield did not understand what I wanted.  :-)

Another option, and probably the right approach to this problem, is to drop the merge module entirely, add a redistributable copy of MSDE 2000 to the installation and use the SQL-DMO installed with it.  All you need to do after that is to figure out how to get a Basic MSI project to execute an MSDE Installation as part of the overall install process.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"Another option, and probably the right approach to this problem, is to drop the merge module entirely, add a redistributable copy of MSDE 2000 to the installation and use the SQL-DMO installed with it.  All you need to do after that is to figure out how to get a Basic MSI project to execute an MSDE Installation as part of the overall install process."

And that's what we have been doing....shell out and run the silent install of MSDE...Not glamorous, but it works :)
0
 

Author Comment

by:Peacemaker
Comment Utility
Can you say "bandade"?  :-)  Do you suppose InstallShield reads these threads?  Take care, arbert.  I'm going to shell out and run silent now.  :-)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now