Executing a SQL Script

[sreeramg]

Hi,

I have a Sql Script with two lines to execute as below. I get an error "Stored procedure not found" (Attached bmp.) I do not have any stored procedures or anything. I have created the database with Install Script and then trying to run the ExecuteSQL with the examples given in Install.org

And the variable szSQL (from ExecuteSQL function) does show some garbage.. Not a STRING??

If you have any clues let me know..

My Sql Script runs fine in Query analyzer.

thanks
-sree

***************my sql script file has these 2 lines**********
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ChannelBar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ChannelBar];
CREATE TABLE [dbo].[ChannelBar] ([ChannelBarName] [char] (20) NOT NULL
,[Description] [char] (50) NULL ,[SequenceNumber] [int] NOT NULL
,[DisplayOnSignOn] [bit] NULL ,[URL] [char] (256) NULL ) ON [PRIMARY];
****************************
Here are the Functions

function BOOL ExecuteSQLScript(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword, svScriptFile)
OBJECT pADOObj, pADOCommObj;
STRING szADOObjID, szADOCommObjID;
STRING svLine, szConnString, szSQL, svString;
NUMBER nResult,nError;
LIST listID;

begin
// Create an empty string list.
listID = ListCreate (STRINGLIST);
// Read the SQL script file into the list
if (ListReadFromFile(listID, svScriptFile) < 0) then // read list from file
MessageBox ("ERROR: Unable to open SQL script: " + svScriptFile + ".", SEVERE);
nError = 1;
return nError;
endif;
// Go through each list item and add it to a string (which will then hold the script)
szSQL = "";
nResult = ListGetFirstString (listID, svString);
while (nResult = 0)
szSQL = szSQL + " " + svString;
nResult = ListGetNextString (listID, svString);
endwhile;

// Be good and clean up your trash
ListDestroy(listID);

// Create ADO Connection Object to connect to the SQL server
szADOObjID = "ADODB.Connection";
set pADOObj = CreateObject(szADOObjID);

// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=" + svDatabaseName;
// Open the ADO Connection
pADOObj.Open(szConnString);

// Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOObj;

MessageBox("szSQL Value = " + szSQL, INFORMATION);
// Execute the call to run the script
try
pADOCommObj.CommandText = szSQL;
pADOCommObj.Execute();
catch
MessageBox("Error",SEVERE);
endcatch;
return TRUE;
end;

*****************************************

function BOOL CreateDatabase(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword)
STRING szADOCommObjID, szADOObjID, szConnSting;
OBJECT pADOObj, pADOCommObj;
begin

// Create ADO Connection Object to connect to the SQL server
szADOObjID = "ADODB.Connection";
set pADOObj = CreateObject(szADOObjID);

// Create the SQL string to complete the connection
szConnSting = "driver={" + svDriver + "};";
szConnSting = szConnSting + "server=" + svServerName + ";";
szConnSting = szConnSting + "uid=" + svUserName + ";";
szConnSting = szConnSting + "pwd=" + svUserPassword;

// Open the ADO Connection
pADOObj.Open(szConnSting);

// Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOObj;

// Execute the call to create the database
pADOCommObj.CommandText = "Create database " + svDatabaseName;
pADOCommObj.Execute();

return TRUE;
end;

[Bloomerville]

The error you are receiving is "invalid hexidecimal characters found". I appears that it does not like the space in the string that is executed.

[namit23]

Hello,

Create an empty text file like 'sqlscript.txt' and paste the contents of your .sql file in the text file.

Then pass the 'sqlscript.txt' file as the svScriptFile arguement for the ExecuteSQLScript function. Should work fine.

Hope this helps...