November 29, 2011 at 4:30 am
Hi everyone
Not sure if this is the right forum, but thought you might know.
I have a .Net application for our data team to do a bunch of things, all code uses stored procedures I created and command to run a SSIS package
EXEC msdb.dbo.sp_start_job N'DATA_do_appends';
When I run this on my dev machine it works fine, but when I move it to the webserver, I get the error:
Unable to index into an object of type Microsoft.SqlServer.Management.Smo.DatabaseCollection
Do I need to install some SQL Server components on the webserver for this to work?
Any ideas appreciated.
Thanks
November 29, 2011 at 6:14 am
Check if Sql Server Management Objects (SMO) is installed on the server.
If it isn't be very careful to install the same version you developed with.
I've had issues with SMO versions 2005/2008/2008R2 compatibility. The problems are not unsurmountable, but can cause a pain. If in doubt post your development version of SMO and your SQL Server version and I'll try to advise.
November 29, 2011 at 6:24 am
Hi
SMO is not installed on the server, the version on my dev PC is
MS SQL Server 2008 Management Objects
Version 10.0.1600.22
Can I install this to make it work?
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978
2008 R2 Feature Pack V: 10.50.1600.1
Thank you.
November 29, 2011 at 6:29 am
Try Control Panel Add/Remove programs (XP) or Programs and Features (Win 7)
You will have an entry
Microsoft SQL Server 2008 Management Objects
version 10.0.x.x
~OR
Microsoft SQL Server 2008 R2 Management Objects
Version 10.50.x.x
I'm not sure what the 2005 version reports as, but it will be version 9.x.x.x
November 29, 2011 at 6:31 am
Hi Tom
Its MS SQL Server 2008 Management Objects
Version 10.0.1600.22
Thanks.
November 29, 2011 at 6:34 am
Jako de Wet (11/29/2011)
HiSMO is not installed on the server, the version on my dev PC is
MS SQL Server 2008 Management Objects
Version 10.0.1600.22
Can I install this to make it work?
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978
2008 R2 Feature Pack V: 10.50.1600.1
Thank you.
This is where you may get the incompatibility between 2008 (version 10.0.x.x) and R2 (version 10.50.x.x)
See if you can find 2008 Management objects to match your Dev.
you can always upgrade later - but you won;t be able to install 10.0 if 10.50 is already installed.
November 29, 2011 at 6:37 am
*** IMPORTANT ***
Make a restore point on the server before installing anything.
November 29, 2011 at 6:39 am
Thanks for the info!
November 29, 2011 at 11:27 pm
Hi Tom
I just double checked the server, it has a newer version than my dev machine
SMO v10.1.2531.0
Do I try and still this version on my dev machine rather?
November 30, 2011 at 1:43 am
If I understand you right, the server already has SMO v 10.1 - This is still the non R2 version, so there should be no problem installing that same version on your dev environment.
Before you try that, you could try some binding redirect changes to your config.
define the old version as zero to <your dev version> and the new version as the server version
This is a simple change, doesn't require installing or rebuilding anything.
You'll need an entry for each of the assemblies you reference in the code. they all have the same public key token. These are the most commonly used ones.
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.ConnectionInfo" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-10.0.0.0" newVersion="10.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.Smo" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-10.0.0.0" newVersion="10.0.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.SqlEnum" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-10.0.0.0" newVersion="10.0.0.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
November 30, 2011 at 4:07 am
Hi, I tried that.
web.config looks like this:
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.Smo" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
<bindingRedirect oldVersion="0.0.0.0-10.0.1600.22" newVersion="10.1.2531.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
I get the following error:
Exception calling "LoadWithPartialName" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.SMO, Version=10.1.2531.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."
I have double checked the server where its running, the SMO is version 10.1.2531.0
November 30, 2011 at 4:15 am
I have noticed that my processing all fails on the first section of the application, that actually imports the excel file into the database, its a powershell script, with the following
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO.SqlDataType') | Out-Null
It seems the error is occurring here.
I can always code around this, but would like not to if possible
**EDIT**
If I remove the powershell script that loads the excel into a DB everything works fine. Is there a way to fix this script? Otherwise I will just create a script to import the data based on the headers in the Excel file.
November 30, 2011 at 4:32 am
Loads of possibilities. Its just a matter of tracking down the right one.
You could try copying all the smo dlls in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies to your web project bin directory, then see if that solves it, as then all dlls are available. If it does then work out why your project can't access the files.
On the Server
1) Is Microsoft.SqlServer.Smo in the GAC (C:\Windows\Assembly) it should be
2) Where is the dll on disk (C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies)
3) Does the web user have read/execute permissions to that directory
In your Development Project
3) Are you copying the Smo dll Locally (Properties Copy To Local - should be false)
4) Check the versions of all smo dlls you reference. All should be the same version.
5) LoadWithPartialName is a System.Reflection method, that can take security policy evidence. Are you calling this directly in your code, or is it embedded deep within the stack trace.
Also it may be worth throwing this question open to a wider forum such as StackOverflow - there's bound to be people with more experience of this kind of thing.
November 30, 2011 at 4:44 am
thanks for all the help Tom, will troubleshoot and let you know.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply