Database

Re: Drop and Log Constraints

8-May-08 23:18

How would I go about enumerating constraints on a table, logging them, and then dropping them with a view to recreating them again later? I assume something in the Microsoft.SqlServer.Management.Smo namespace would be of use, but a perfunctory Google wasn't too helpful, and I have a 14h00 deadline, and it's now 11h20.

Pits fall into Chuck Norris.

Mycroft Holmes11-May-08 0:30

Need Help with ADODB.Connection_InfoMessage Event in VB6

11-May-08 0:30

I don't know about constraints but we have done the following with indexes.
In Enterprise Manage create an index, BEFORE saving the change you can get SQL to script the proposed changes, save to a file or clip. You can then work over the code to meet your requirements. Just like excel macros Laugh | :laugh:

And yeah I guess this is way to late for the deadline, but it is Sunday afternoon here now and....

[edit] script icon is next to the primary key icon [edit]

Never underestimate the power of human stupidity
RAH

valkyriexp8-May-08 13:58

valkyriexp

8-May-08 13:58

I appreciate if some one could help me with using InfoMessage Event of ADODB.Connection object in VB6 and SQL server.
If I pass a single SQL Print command to the ADO object, it will fire the Event and works fine, but a print command among other commands doesn't work. Am I missing something??
here is the code

Thanks
Arash
__________________________________________________________________________________________
Private WithEvents cn As ADODB.Connection

Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox pError.Description
End Sub

Private Sub Comm_UPD_Rec_Click()
SQLAdd = " DECLARE @Key int " & _
"SET @Key=(select (max(task_id)+1) from task) " & _
"insert into task (task_id,...)values (@Key ,...) " & _
" PRINT @Key"

Dim cmd As New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = SQLAdd
Set cmd.ActiveConnection = cn
out = cmd.Execute
End Sub
__________________________________________________________________________________________

Re: Need Help with ADODB.Connection_InfoMessage Event in VB6

Mycroft Holmes8-May-08 21:12

Re: Need Help with ADODB.Connection_InfoMessage Event in VB6

8-May-08 21:12

Might I suggest you move from SQL strings to a stored procedure based structure, you are lamost there with your string.

A couple of things (I'm not up on VB6 or ADODB any more) so
I don't think Print will return anything @key.
You are using a sub and out is not declared (use a function and return the output of cmd.execute)
executing a sql string using your structure is just weird (and wrong for security reasons). most people just create a string like "Insert Table (fields)Values("+ varvalue + ")" and exc the string (This is still wrong but simpler)

Take your SQL and paste it into Query analyer/Enterprise manager and see if it runs, I dout it.

Never underestimate the power of human stupidity
RAH

valkyriexp9-May-08 6:18

valkyriexp

9-May-08 6:18

The SQL is a part of a larger SQL statement that the client program generates and it works fine, I mean it does what it's supposed to do and with a lot of parameters and loops that i have it's much easier to create it on the client program rather than on the server.
I was expecting to catch the event when SQL executes PRINT or RAISERROR but it doesn't. If you pass a simple SQL command like
PRINT 5
The event fires and program shows the message box,
But when you have
Declare @key int
SET @key=5
PRINT @key
Nothing happens. Strange?

Thanks anyway
Arash

Re: Need Help with ADODB.Connection_InfoMessage Event in VB6

valkyriexp9-May-08 10:35

valkyriexp

9-May-08 10:35

I found the Answer
the SQL command shall be enclose in a Begin...End and SET NOCOUNT ON shall be used.
so the SQL would be like

SQLAdd = " Begin SET NOCOUNT ON; DECLARE @Key int " & _
"SET @Key=(select (max(task_id)+1) from task) " & _
"insert into task (task_id,...)values (@Key ,...) " & _
" PRINT @Key; End"
I also tryed placing Select @Key at the end of the SQL command, but without Begin...End and SET NOCOUNT ON
rs.open SQLAdd will not actually open the recordset and any attempts to read the rs will result in Error Object Closed. But if you place Select @Key after the End, rs will contain the @key.

Arash

INFORMATION_SCHEMA.COLUMNS vs. sys.columns

Brady Kelly8-May-08 2:51

8-May-08 2:51

Anyone care to comment on the differences between these? A preliminary Google yields very little.

Mycroft Holmes8-May-08 21:22

8-May-08 21:22

I beleive the views are now the "preferred" method of getting at the schema, M$ is big brothering you again, Sigh | :sigh:

typical dumbing down of the database. With the schemas you do not have to really understand the underlying tables and structures - newbies also never learn that there is an underlying environment available to them.

I seem to recall they made noises about hiding the underlying tables but nothing ever came of it.

Never underestimate the power of human stupidity
RAH

Brady Kelly8-May-08 21:54

8-May-08 21:54

Both of them are views, so besides decrying what is elsewhere considered good practice, because the bad M$ does it, you haven't really contributed to this thread.

Pits fall into Chuck Norris.

Mycroft Holmes8-May-08 22:07

8-May-08 22:07

Interesting, I always thought the sys.objects, sys.columns etc were tables.

I think having to rat through the sys objects (I had trouble writing that) is one of the best way of learning how the SQL is put together. When everything is layed out on a platter(view) life gets too simple. This is fine for users but a developer should have to get down and dirty in SQL.

Never underestimate the power of human stupidity
RAH

Brady Kelly8-May-08 22:22

8-May-08 22:22

You're thinking of sysobjects and syscolumns, no full stop. I seriously doubt this is a dumbing down exercise, as navigating sys.columns is not much easier than navigating syscolumns, but MS advise uysing the abstraction layer to preserve compatibility if they move away from or change the underlying syscolumns table. I take their advice as a hint that they are planning on changing things, and when I write robust code that must access schema information, I will choose the more reliable views over the less reliable tables.

Pits fall into Chuck Norris.

Rob Graham10-May-08 7:36

Rob Graham

10-May-08 7:36

From Wikipedia:
"In relational databases, the Information Schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands, such as the SHOW command of MySQL and the DESCRIBE command of Oracle."

sys.columns is a version that predates (actually comes from Sybase, from whom Microsoft bought SQL Server) the ANSI standard. So, all ANSI compliant relational databases will have the INFORMATION_SCHEMA viesw as defined in the standard, but only SQL Server (and possibly still Sybase on Unix) will have sys.columns.

Brady Kelly11-May-08 10:31