June 6, 2011 at 4:58 am
HI All
I am new to scripting in ActiveX so please forgive me if this doesn't make sense.
There is a script which someone had wrote before I started to script out all objects in a databases to a file every night.
I have parsed the script via SSIS and get the attached error message.
I have removed CopyAllObjects and placed in each copy object and it fails on the tables and views objects.
I have run the same script on a 2005 copy of the DB and it runs through absolutely fine and the comparability level is 100 on 2008 and 90 on 2005 so setting it to 80 as the error says shouldn't really make a difference.
I have used Apex SQL Diff to take a look at the differences between the 2008 and 2005 versions of the databases to which nothing using *= or =* and looked through the views to again ensure they are not using non-ANSI joins.
The script is below
Dim oSS
Dim oDb
Dim oT
Set oSS = CreateObject("SQLDMO.SQLServer")
Set oDb = CreateObject("SQLDMO.Database")
Set oT = CreateObject("SQLDMO.Transfer")
oSS.LoginSecure = True
oSS.Connect "SQL1"
Set oDb = oSS.Databases("Database1")
oT.CopyAllDefaults = True
oT.CopyAllFunctions = True
oT.CopyAllRules = True
oT.CopyAllStoredProcedures = True
oT.CopyAllTables = True
oT.CopyAllTriggers = True
oT.CopyAllUserDefinedDatatypes = True
oT.CopyAllViews = True
oT.CopyData = SQLDMOCopyData_False
oDb.ScriptTransfer oT, 4, "\\fileserver\databasescripts\database1"
Set oSS = Nothing
Set oDB = Nothing
Set ot = Nothing
Any help would be appreciated.
Thanks
Ant
June 6, 2011 at 6:43 am
There's probably a query in the tables or views that uses an older-style join. You mentioned you checked the views, but did you check for things like calculated columns in a table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2011 at 7:58 am
No I didn't look at calculated columns, will take a look and re-post.
June 6, 2011 at 8:04 am
ok so scripted all tables and views from the db using SSMS and searched for *= and =* to which brought back nothing.
as SQLDMO is no longer a feature in 2008 and SQLSMO has taken over, how would I go about changing the script to do what I need it to do?
Sorry I'm not the best at coding, give me T-SQL any day.
June 6, 2011 at 1:17 pm
If you're ditching SQLDMO you might as well ditch VB Script as well and move to PowerShell. There are tons of examples online of scripting out DB objects using PowerShell and SQLSMO.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 12:28 am
Yeah I have moved on to powershell anyway, just didnt know if there was a quick way to change between DMO and SMO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply