Jump to content

Msde Sql Question Create View Not Working


Recommended Posts

I have been doing SQL querys and SQL Database creation scripts for about 6 months now but this is the first time I have ever tried to use the CREATE VIEW sql command.

I have tried many different ways but I can't get it to work. My last of many attempts is

Func _t1CreateView()
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; Data Source=xx.xx.xx.xx; User ID=" & $sSQLID & "; Password=" & $sSQLPW & ";")
$sqlRs = ObjCreate ("ADODB.Recordset")
;Fails on this next line every time
$sqlRS.Execute("CREATE VIEW Misc1 AS SELECT * FROM ImageSpecs")
$sqlRS.open("Select * from Misc1", $sqlCon)
With $sqlRS
    If .RecordCount Then
        While Not .EOF
            $t1Identifier = .Fields('ImageName').Value
            MsgBox(0, "Returned", $t1Identifier)
        .MoveNext
        WEnd
    EndIf
EndWith
$sqlCon.Close
EndFunc

Does anybody have any ideas on how to get the Create View line to work?

Thanks,

Mike

Link to comment
Share on other sites

I have been doing SQL querys and SQL Database creation scripts for about 6 months now but this is the first time I have ever tried to use the CREATE VIEW sql command.

I have tried many different ways but I can't get it to work. My last of many attempts is

Func _t1CreateView()
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; Data Source=xx.xx.xx.xx; User ID=" & $sSQLID & "; Password=" & $sSQLPW & ";")
$sqlRs = ObjCreate ("ADODB.Recordset")
;Fails on this next line every time
$sqlRS.Execute("CREATE VIEW Misc1 AS SELECT * FROM ImageSpecs")
$sqlRS.open("Select * from Misc1", $sqlCon)
...

Does anybody have any ideas on how to get the Create View line to work?

Thanks,

Mike

Not an expert here but I think that this:

$sqlRS.Execute("CREATE VIEW Misc1 AS SELECT * FROM ImageSpecs")

should be run against the connection and not the recordset:

$sqlCon.Execute("CREATE VIEW Misc1 AS SELECT * FROM ImageSpecs")

Thats how I do it in VB..

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...