|
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.
|
|
|
|
|
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 
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
|
|
|
|
|
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
__________________________________________________________________________________________
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Anyone care to comment on the differences between these? A preliminary Google yields very little.
|
|
|
|
|
I beleive the views are now the "preferred" method of getting at the schema, M$ is big brothering you again,  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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Enlightenment at last! Thanks Rob. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Pits fall into Chuck Norris.
|
|
|
|
|
hi
Friends
I am working on a asp.net application
in one of the aspx page i am having a combobx
in which i want to display a concatenated string ..
that means i want to concatenate four fields from the database and display it in the combo box
Can i get those values in a datareader so that i can assign it..
Please suggest how to solve this problem
Example
combobox value is (a1,a2,a3,a4),(a5,a6,a7,a8),(a9.a10.a11.a12) where all the fields(a1,a2,a3,a4.......) are coming from the database
If You win You need not Explain............
But If You Loose You Should not be there to Explain......
|
|
|
|
|
In your SQL you can concatonate the fields together to give you an additional column.
select cola + ',' + colb + ',' + colc + ',' + cold as 'ConcatCol'<br />
from tablename
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have a table which have person's name as Prefix, firstname, lastname, address as three
different columns.
I want to get all records when fire a query like
SELECT * from Persons where name= 'abcd'
I want such query which can search in all three columns, it means abcd can be in
Prefix, firstname or lastname column.
Actually I have a page in which there are some text boxes which accepts some inputs and
based on that inputs displays matching records in GridView.
Suppose someone entered 'Ind', so my query should find records in all three columns of name.
And if someone entered record in first text box say name and nothing is entered in address text box, then
the records matching with that name with all addresses should get displayed.
How can I do that?
Thanks in advance, 
|
|
|
|
|
I have joined three columns as
SELECT (Prefix+' '+firstname+' '+lastname) from Persons
|
|
|
|
|
Not too sure what you wanted, but here's what I think you are asking for:
SELECT (Prefix+' '+firstname+' '+lastname) from Persons Where Prefix like '%abcd%' or firstname like '%abcd%' or lastname like '%abcd%'
|
|
|
|
|
thanks it worked! 
|
|
|
|
|
I have an application that uses the OracleClient namespace to connect to Oracle database. the application opens only one connection and keep it open as long as the application is running.
queries that are used in the application are select, insert, update and delete ( approximately 50 queries per minute).
for short period after running the application, everything does OK, but after a long period this application reserves a high CPU percentage from the Oracle Database.
I'm using a static class to represent the Database connection and methods that are used to execute queries, which means that once the method scope is ended the application must garbage collect any resource used at that method.
my question is, what could cause the high usage of the CPU on the Oracle client even though the application is ideal but the connection is still established?
Best Regards,
Ahmad Zuhd
|
|
|
|
|
|
By writing a trigger that does something like delete from mytbl where mydate = '1/1/1900' ???
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
thx....... i have nearly 50 columns in my table and nearly 35 columns "datetime".... how to proceed this?. i need this trigger. i m not familiar with triggers... kindly provide me the exact TRIGGER FUNCTION?
|
|
|
|
|
OK - I got a 1 for helping you because ?
The books online and google explain how to write a trigger. I'd help more, but then I'd just get another one vote for my trouble.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
WASTEEEEEEEEEEEEEEEEEEEEE
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
