|
Hi all
I wonder if it is possible to get information about a specific SQL Server database? What I need is a way to get a list of all the tables in the database and also the names of all columns in all tables. I also need a way to get the datatype of each column.
Is this possible in SQL Server? (I am using MSDE)
|
|
|
|
|
Yes - the system tables (e.g. Sysobjects) hold details of the tables, views etc. and you can get the SQL of stored procedures and views from syscomments
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
SQL DMO is a great library for getting Sql Server metadata. See: http://www.sqlteam.com/item.asp?ItemID=9093[^].
It gives you a bit more flexibility than running queries like "select * from sysobjects where type='u'" and so on. It gives you a navigable heirarchy as well. You can get schema info using the .NET framework, but for what you're describing, I'd use DMO.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
True - but since most enterprise class database platforms have their own versions of the system tables you can wrte a plug-in based system that could be platform independent - rather like the Database Build Wizard[^]
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Hi Everyone
Im a Beginner in using DTS
i want to make a package in SQL Server That execute an sql query:
- If this query returns rows, i want to take the first field for every row
and give it to a second job that execute another query using this field in a
loop until the number of fields ends
- And if this query doesent return any rows, i want to skip the others steps
that follow it
How Can I Do It????????
|
|
|
|
|
Hi Maro,
I came across an article today that might help you get started.
http://www.sqlteam.com/item.asp?ItemID=6881
The article was actually about trapping errors, but there are a couple of lines that may point you in the right direction:
Basically, try this:
declare @RC int
set @rc = 0
select Column1 from Table1
select @RC = @@ROWCOUNT
if @RC > 0
begin
{do the second job}
end
I realize this doesn't solve your entire problem, but I hope it at least points you in the right direction.
-DPR
|
|
|
|
|
I'm tryingt o use this statement
Adodc.RecordSource = "UPDATE Volenteers SET (Time_In) = '0' "<br />
Adodc.RecordSource = "UPDATE Volenteers SET (Time_Out)= '0' "
It worked once!!! now after i write some rwecords to te table, and try to clear those specific fields, thet don't clear, any ideas??
VB6 front end w/ MS access 2k DB
|
|
|
|
|
I'm just taking a stab here but why are you assigning a recordsource property when in actuality you're executing a sql action statement? If you're using VB6 and ADO, you can use the connection object's 'execute' method to execute a sql statement, after which you can refresh your recordsource.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
I have an application the adds records to an MS Access database. When the DataAdaptor.update command is issued I get a Syntax error in insert into statement. I am using command builder and only get the error on one table. That table has two fields which are the problem. They are called "Password" and "Usergroup." If I rename the fields it works without any problem. So why not just rename them? Simple right. No, the database is used by another application and chances of getting it changed is slim at best.
Any ideas as to how to work around this?
Any help would be greatly appreciated.
Mike 
|
|
|
|
|
|
Password is a reserved word Can't use it
|
|
|
|
|
Then how is it used by the other application?
I don't use Access but, maybe it permits escaping of column names with square brackets. e.g. [Password]
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
I have a access table and want to export the data in the table to a html file every night. i also need to clear 2 fields in the table after the data has been exported. Any ideas on how to do this?
I'm using ADO on the VB6 forms
|
|
|
|
|
Write a VBScript and use the Command scheduler.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Where I can get the subj? Thanks in advance
|
|
|
|
|
I have a C# application for generic database access that does all sorts of extra processing. It is a port of an existing COM application written in C++.
The application uses objects from Microsoft.Data.Odbc namespace.
So I'm opening an OdbcConnection
and using an OdbcDataApaptor for retrievals, and an OdbcCommand for executing updates using ExecuteNonQuery.
All is working fine except for one thing involving the SQL Server RaiseError command being executed in a trigger.
The code I'm using in the application for update commands is this:
public long ExecuteChangeQuery(string sSQL)
{
long lResult;
try
{
m_cmd.CommandText = sSQL;
lResult = m_cmd.ExecuteNonQuery();
}
catch(OdbcException e)
{
// log the exception
throw e;
}
return lResult;
}
Under normal circumstances everything is fine. And if a normal error occurs, such as a SQL syntax error, etc. the exception is caught as expected.
However when the SQL server trigger calls RaiseError with a severity level of 16, which should throw an exception. Nothing happens. No exception is thrown and the call to ExecuteNonQuery simply returns -1 to indicate that a rollback occurred.
The existing COM application works properly with the same trigger.
Any ideas as to what's going wrong, or what I'm not doing?
Help greatly appreciated.
|
|
|
|
|
I have a VB 6 Application that writes to a ms access DB. My connection works fine ( using ADO) problem is I'm trying to do a search on the table using a varible. the varible is string. I'm not sure if i have the syntax correct. PLEASE HELP
Here is the code
Private Sub CmdSearch_Click()<br />
Dim strSearch As String<br />
<br />
strSearch = lblSearch.Caption<br />
<br />
adoRecords.RecordSource = "SELECT * from Volenteers where In [last_name] Like strSearch ; "<br />
<br />
adoRecords.Refresh<br />
<br />
End Sub
I get a error of syntax error in FROM clause
strSearch is from a text box from a dialog form
i had the same set up in a different program almost same sytax, and it works there I'm lost
any help would be appreciated
thanks
|
|
|
|
|
Hm - I would have written it as:
for exact matches -
"Select * from Volunteers Where [Last_Name] = '" & strSearch & "'"
for wild cards -
"Select * from Volunteers Where [Last_Name] Like '" & strSearch & "%'"
I believe this will work with access, try the query builder tool.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
tried get error in from clause
under the sql i have adorecords.recodset.refresh
that gets highlighted
???
|
|
|
|
|
Try setting a breakpoint on the line where to set your sql variable. Run the program in the debugger and set a watch for your sql variable. Once you know *exactly* what your sql statement looks like, run it in access to make sure it is syntactically correct. You may be missing a "'" or something -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Hey all, just for giggles, I was trying to figure out how these works. I created the following table:
Create Table Emps(<br />
EmployeeID int identity(1,1),<br />
EmpName varchar(100), <br />
ManagerID int, <br />
ActiveFlag char(1)<br />
)
insert into emps(empname)values('Big Boss')<br />
insert into emps(empname, managerid)values('David', 1)<br />
insert into emps(empname, managerid)values('Holly', 2)<br />
insert into emps(empname, managerid)values('Samwell', 3)<br />
insert into emps(empname, managerid)values('Ian', 4)
-- set everyone active
update emps set activeflag = 'Y'
CREATE TRIGGER EmpTrig ON emps For UPDATE <br />
AS<br />
UPDATE Emps SET ActiveFlag= i.activeflag <br />
FROM (Emps e INNER JOIN Inserted i ON e.managerid = i.employeeid)<br />
GO
The idea was that this would set everyone in a heirarchy "Inactive" if the manager was set inactive. Unfortunately, it does this only one level deep. How can I enable recursive triggers?
Thanks in advance -
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
alter database <DatabaseName>
set RECURSIVE_TRIGGERS on
Wout Louwers
|
|
|
|
|
Hello,
I'm writing a little app for a doctor. Each patient can have many appointments, and each appointment can have many prescripted drugs, like the tables below:
Patient
-------
ID_patient (key)
Appointment
-----------
ID_appointment (key)
ID_patient
Drugs
-----
ID_drug (key)
ID_appointment
If I want to delete a patient, I have to delete all his appointments, and all their drugs.
A simple DELETE FROM Appointment WHERE ID_patient=@PatientID; query will delete all my appointments. How can I delete all the Drugs for each appointment in a single query, without using a DataReader to walk all the appointments and executing a DELETE query for each appointment?
Any help will be greatly appreciated. Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Use a stored procedure to contain the delete logic or, as a second choice, place triggers on the patient table to cascade delete all dependent tables' associated records. Wait, you're not using some weak open-source dbms that doesn't support triggers, are you? 
|
|
|
|
|
TimWallace wrote:
Wait, you're not using some weak open-source dbms that doesn't support triggers, are you?
No 
I'm using a VistaDB database. But I found how to do it, just ask my relationships to cascade deletes.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
