Error in Executing sqlcommand using smo.database.ExecuteNonQuery in vb6

Question

Hello,

I have created a COM Wrapper class in vb.net that includes smo objects .In that class I have included smo.database.ExecuteNonQuery method.

I included that dll in vb6 project.On Calling smo.database.ExecuteNonQuery from vb6 it is not executing the query.

Kindly suggest the solution.It will be a great help.All other functions are working fine.

Code snippet

 VB.net Class Library(smolib)

Database Class(my own class)

added refrences 

Microsoft.SqlServer.Management.Smo

Microsoft.SqlServer.Management.Common

Public Sub ExecuteNonQuery(Sqlcommand As String)

Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(Sqlcommand)

End Sub

VB6 Code

Dim database as new smolib.Database

database.ExecuteNonQuery "Alter table table_name drop constraint Drop Column Column_name"

This ExecuteNonQuery not working.

Please help.

Answer 1

Hello,

Please, could you tell us whether you are getting an error message ( if yes, could you provide the full error message ? )

I am not sure that SMO is working with non managed code ( i am not sure that VB6 is providing managed code ).

How are you providing the name of your database to your ExecuteNonQuery method ?

Please, could you provide the version ( 2000,2005,2008,2008 R2,2012 ) of your database ?

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

PS : i would suggest to have a look at this link :

http://msdn.microsoft.com/en-us/library/ms205782(SQL.100).aspx 

How your code is knowing the instance to connect and the database to modify ?

I have used VB6 since its release upto 2003 , but my remembrance is poor ( 10 years ago, it is far...  even if i have a good memory)

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

This thread appears to be a duplicate of the one you placed in the TSQL forum.

But:

Alter table table_name drop constraint Drop Column Column_name

is not a valid SQL statement

---

Chuck

Answer 3

Error am getting is "ExecuteNonQuery failed for database_name".

Answer 4

sql statement is 'Alter table table_name Drop Column Column_name"

Answer 5

VB6 CODE

Dim database as smolib.Database

Dim server as new smolib.server

set database=server.Databases("HQAdmin")

database.ExecuteNonQuery "Alter table Item  Drop Column Price"

My actual code

Answer 6

Shouldn't this:

database.ExecuteNonQuery "Alter table Item  Drop Column Price"

Be this:

database.ExecuteNonQuery ("Alter table Item Drop Column Price")

---

Chuck

Answer 7

And if you are going through all the trouble of using SMO, why are you not using the Table/ColumnCollection/Remove method?

---

Chuck

Answer 8

the problem is not only for this sql statement.there are several other sql statements which i need to execute like removing the constraints from the table.

Answer 9

try prefixing the tablename with its schemaname

database.ExecuteNonQuery ("ALTER TABLE dbo.Item DROP COLUMN Price")

I assume your account actually has permissions to alter tables?

Also there should be more to that error message than just "ExecuteNonQuery failed for database"   - where is the rest of the error - take a look at the inner exception

---

Chuck

Answer 10

You can also ask your query on this forum, it may help you.

http://www.sqlservercentral.com/Forums/Topic594345-22-1.aspx

Answer 11

Any specific reason why you are using COM Wrapper class?.  Also please take a look at VB6 support policy http://msdn.microsoft.com/en-us/vstudio/ms788708.aspx

Can you post sample project in this forum so that community members can review the code and help you out.

---

Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.