StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Incorrext syntax near 'sname'

Incorrext syntax near 'sname'

Tim Dol — Wed, 28 Jan 2009 10:17:31 GMT

I have a package file that I have deployed on 5 or 6 different databases without any problems, however I have two database where the attached error occurs and I can't figure out what the problem is. Any earlier version of the package, a week ago, worked fine on these two databases and now the new one and the actual original pacakge that worked, has failed.

Any Ideas? Can you suggest how I can narrow down the problem?

Thanks,

Tim

RE: Incorrext syntax near 'sname'

Trent L. Taylor — Wed, 28 Jan 2009 10:17:31 GMT

For those wondering what happened here, the database was originally on SQL Server 2000. It was then moved to SQL Server 2005, however, the server compatability wasn't changed. Since the compatability level was left at 2000, yet it was being hosted in a 2005 server, the DDT attempted to execute the extended properties query for 2005 yet the database wouldn't support those queries since it was set to 2000 compatability. To change this, just do the following:

Right-click the pre-exiting database from with Sql Server Management Studio and then click properties
Go to Options
Set the Compatibility level to 2005

RE: Incorrext syntax near 'sname'

Tim Dol — Wed, 28 Jan 2009 09:34:31 GMT

I will email you the database and package file shortly.

Thanks,

Tim

RE: Incorrext syntax near 'sname'

Trent L. Taylor — Wed, 28 Jan 2009 09:27:20 GMT

Tim, it would be impossible for me to diagnose what yoy may be experiencing with this information. I will need, at the veryleast, your PKG file so that I can deploy it here and see if it is reproducable. If so, then I will be able to debug this and try and determine what you are running into.

Also, we are about to post a new beta that has full SQL Server 2008 support for the DDT as well as all other areas of the framework. In the process of updating this, we moved to SMO 10 which required that we make a few modifications to the migrator class.

Finally, the environment to which you are deploying can also make a difference. If there is something with a pre-deployed database that is unexpected by SMO and the migrator class, then this could be a factor as well. So let's start with a PKG file and go from there.

RE: Incorrext syntax near 'sname'

Tim Dol — Wed, 28 Jan 2009 08:58:28 GMT

Some additional Info....

I traced the database migrator code and discovered the function causing the problem (GetListOfViewsSprocsAndUdfsToDrop).

See attached screen shot.

This is quite urgent so any help would be appreciated.