| Author |
Topic |
|
JimiC
Starting Member
29 Posts |
 Posted - 2003-09-04 : 04:36:03
|
| hi, this has been annoying me for a few days now, and i simply dont know why its not working.im writing a setup page for a site, that given the correct login information connects to the desired SQL server instance, and creates the tables and stored procs required for the rest of the site.so, im reading the script for the database creation in through FSO. (This is working, in any case ive tried hard coding simple queryies)im opening a connection with ADO, and simply executing the string. ive tried defining the command as adCmdText, and defining the CommandText as my sql string but it seems to make no difference.running the same sql, logged on as the same user in query analyzer this works perfectly. through ADO no error is returned, but nothing is created either.can someone please tell me where im going wrong, code snippets follow:strScriptPath = Server.MapPath("create.sql")If objFSO.FileExists(strScriptPath) Then set objFile = objFSO.GetFile(strScriptPath) set objEdit = objFile.OpenAsTextStream() strSQL = objEdit.ReadAll-------------------snip-------------------On Error Resume NextcnnCreate.Open "Provider=SQLOLEDB;Data Source=" & Path & ";" _& "Initial Catalog=Formacion;User Id=" & User & ";Password=" & Pass & ";"cmdCreate.ActiveConnection = cnnCreatecmdCreate.Execute(strSQL)----------------------Making things work by dint of dinting. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-09-04 : 04:44:52
|
| My guess....Your SQL Script has "GO" statements in it.GO isn't actually a TSQL command, SQL Server doesn't know how to interperet it. GO is used to split batches of code inside Query Analyzer. When you run some script, QA parses out the code into batches using the GO delimiter and fires them off one by one.So, in your code you need to do the same. Look for instances of GO on a line by itself and use that to split the code, then execute it block by block.Damian |
 |
|
|
JimiC
Starting Member
29 Posts |
Posted - 2003-09-04 : 05:07:25
|
| erk .. block by block ?ok i had suspicions it would be something like that, in theory then apart from the 'GO's the procedure is correct?/me removes the GOs----------------------Making things work by dint of dinting. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-09-04 : 05:20:44
|
| Yes everything else is fine.Don't just remove the GOs, they are there because somethings need to be in their own batch. Use a split function to create an array of batches and execute them.Damian |
|
|
|
JimiC
Starting Member
29 Posts |
Posted - 2003-09-04 : 05:30:52
|
| yeah by removing GOs i meant for a test ..and its not working :(ive just redifined the SQL to something very basic to see if thats the problem, im currently executing:--------------------------strSQL = "CREATE TABLE [dbo].[MEH] ([ID] [Int] NOT NULL)"cmdCreate.Execute(strSQL)--------------------------again with exactly the same result, no error and no new table.hrm, thats a point, i say its returning no error, this is how im checking that:--------------------------If cmdCreate.Errors.Count = 0 Then Response.Write("·--·Base de Datos creada correctamente.<img src=""../images/tick.gif""><br>")Else For Each errCreate in cmdCreate.ActiveConnection.Errors Response.Write("<b>·--·" & errCreate.Description) Response.Write("( " & errCreate.Number & " )</b><img src=""../images/cross.gif""><br>") NextEnd If------------------------------------------------Making things work by dint of dinting. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-04 : 05:49:30
|
Try executing the script from the connection directly:cnnCreate.Execute strSqlScriptAlso try commenting the "On Error Resume Next" statement and see if the page returns an error.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-04 : 06:08:29
|
| Maybe that 'On Error Resume Next' hides errors from your sight.Secondly, if cmdCreate is ADODB.Command object then yoursyntax is incorrect. Should be like this:cm.ActiveConnection = cncm.CommandType = adCmdTextcm.CommandText = "CREATE TABLE [dbo].[MEH] ([ID] [Int] NOT NULL)"cm.Execute |
|
|
|
JimiC
Starting Member
29 Posts |
Posted - 2003-09-04 : 06:23:06
|
| thanks allStoad .. im sure thats the first thing i tried, however i must have changed something in the process of beating this with a big stick.going back to the syntax:commandtype = adCmdTxtcommandtext = strhas it working now, thanks all, thats something thats been annoying me for days sorted in minutes :D----------------------Making things work by dint of dinting. |
|
|
|
|
What on earth am i doing wrong?