Running SQL Script from sql file through Addon

[Former Member]

Hi All,

i have got 5 views which are stored in 5 different sql files

These views, i need to run through Addon

How can i do this?

Regards,

Mahendra

[Former Member · Accepted solution]

hi,

you can run sql script from SDK.

Dim ConStr3 As String = "Server=" & ServerName & ";DataBase=" & CompanyDB & ";uid=" & UserName & ";pwd= " & Password & " ;Integrated Security=SSPI"

Dim Con3 As New SqlConnection(ConStr3)

Con3.Open()

cmd3.CommandType = CommandType.Text

cmd3.Connection = Con3

objReader = New System.IO.StreamReader(System.Windows.Forms.Application.StartupPath & "\sqlscript1.sql")

cmd3.CommandText = objReader.ReadToEnd

cmd3.ExecuteNonQuery()

objReader.Close()

Con3.Close()

Hi please try one view in one page , if its working then put others in one page & try.

this coding is working for me.

Regards,

Avijit

[Former Member · Accepted solution]

hi,

please add this line before code

Dim objReader As System.IO.StreamReader

regards,

Avijit

[Former Member · Accepted solution]

Thanks a lot Petr ,wesley souza ,Avijit for ur replys.

I am trying ur solution.

Regards,

Mahendra

[Former Member · Accepted solution]

Hi Avijit,

i have tried ur solution, it works fine

but if i have a view where i m using 'Go' as shown below it gives me an error as

"Incorrect syntax near Go, 'Create View' must be the first statement in a query batch"

Sample View:

If object_id('Sample1') is not null

Drop View Sample1

Go

Create View Sample1

As

Select * from ocrd where cardtype='s'

awaiting for ur reply..

Regards,

Mahendra

[Former Member · Accepted solution]

hi,

i know it will happen...

that why i told u use separate script.

script 1:

If object_id('Sample1') is not null

Drop View Sample1

script 2:

Create View Sample1

As

Select * from ocrd where cardtype='s'

and call ur script twice time just repeating ur code.

regard,

avijit

[Former Member · Accepted solution]

Hi Avijit,

It works.

Thanks and Regards,

Mahendra

[Former Member]

If you need use view, the view must be created on sql server. The you can use the view normaly from addon (if the view has proper rights). So your question is, how to create view from sap addon on sql server from file. Through sap recordset it is not possible, but you can use standard ADO recordset and read the file with view defition and run it in recordset.

[Former Member]

you can do a method for execute your script. I already did this, but i make an txt file whit a instructios of SQL.

ex:

method:

void CreateView()

{

string FullSQL = string.Empty;

string sLine = string.Empty;

StringReader _StR = new StringReader(Properties.Resources.ScriptView);

ArrayList _Text = new ArrayList();

try

{

while (sLine != null)

{

sLine = _StR.ReadLine();

if (sLine != null && sLine != "GO")

{

_Text.Add(sLine);

FullSQL = FullSQL += '\n' + Text[Text.Count - 1].ToString() ;

}

else

{

if (FullSQL == "\nSELECT EXISTE = 1 FROM SYSOBJECTS WHERE XTYPE = 'V' AND NAME = 'view name'")

{

DataSet Ds = Table.GetDataSet(FullSQL);

FullSQL = string.Empty;

if (Ds.Tables.Count > 0 && Ds.Tables[0].Rows.Count > 0)

{

if (Ds.Tables[0].Rows[0][0].ToString() == "1")

{

sLine = null;

}

}

_Text.Clear();

}

else

{

Table.ExecuteQuery(FullSQL);

FullSQL = string.Empty;

_Text.Clear();

}

}

}

_StR.Close();

}

catch (Exception ex)

{

throw new exception;

}

}

file :

SELECT EXISTE = 1 FROM SYSOBJECTS WHERE XTYPE = 'V' AND NAME = 'BMH'

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[BMH]

As

SELECT DATA = '',

field = '',

field =''

i hope help you....

att,

[Former Member]

Hi..

I thing this is not possible..

Regards..

Billa 2007