|
Look at the INFORMATION_SCHEMA.COLUMNS to find the data type of columns in the database. (Assums SQL Server - You didn't say, but it is the most common database used on this forum)
|
|
|
|
|
Hi Colin
Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
|
|
|
|
|
RichardBerry wrote: Yes Using SQL server, but how do I do this? I looked, but don't seem to find a table with this name (INFORMATION_SCHEMA.COLUMNS)?
It is a view, not a table.
It is on the Master database and will be picked up regardless of which database you are in.
I'm also curious, did it not occur to you just to type SELECT * FROM INFORMATION_SCHEMA.COLUMNS or search for references to it on the internet?
|
|
|
|
|
Hi Colin
Thanks that was exactly what I was looking for.
Colin Angus Mackay wrote: I'm also curious, did it not occur to you just to type SELECT * FROM INFORMATION_SCHEMA.COLUMNS or search for references to it on the internet?
No it did not occur to me, since I looked through all the databases on the server, and could not find a table with that name, so I thought perhaps this was a table that was not in my database. I had never heard of a 'view' before, so I am currently GOOGLING 'SQL View' to learn more about that. It is sometimes difficult for a NOVICE (myself) to ask the right questions or look in the right places for information.
|
|
|
|
|
I use ExecuteReader and then ask the DataReader for the DataTypes.
DataReader.GetSchemaTable()
or
DataReader.GetFieldType()
|
|
|
|
|
RichardBerry wrote: Is it possible to write a query that will return the datatype of each field
In sql Server, there is a system stored procedure (sp_columns ) which returns column information for the specified table or view. You can execute that to get the DataType of the columns.
Exec sp_columns [@TableName]
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi John
Thanks for your reply. I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE..... Etc
It would actually be great if I could get all table data for the database in the above format.
When executing 'Exec sp_columns [@TableName]' I tried replacing 'TableName' with various strings but cant seem to get any data back. I tried *, vektron.scheme.units, scheme.units, units. But none seemed to work. Typically to execute a normal Select query, I would use:
SELECT * FROM vektron.scheme.units
Any idea what I could be doing wrong?
|
|
|
|
|
RichardBerry wrote: I tried running that stored procedure from MS Query, and get the following fields returned,but with no data.
return's no data? How come? It works fine in my system...
I have a table with the name tblProducts and I executed the stored proc as below...It return's the complete information...
Exec sp_columns [tblProducts]
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi John
Got it right - my syntax was wrong - I was putting in the '@' before the table name.
BTW, is there a way to get more the info on all tables at once? I tried
Exec sp_columns [*] but that gave no data back?
Thanks for your help!!
|
|
|
|
|
RichardBerry wrote: BTW, is there a way to get more the info on all tables at once?
EXEC sp_tables will list all the tables in the current Database
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Hi,
I have a table with more than one row for a particular employee. Want to take one by one and put it to different columns in another table.
This example below will make it clear, please check.
e.g.,
First table
Emp_id dependents
100 aaa
100 bbb
200 ccc
200 ddd
second table
emp_id dependent1 dependent2
100 aaa bbb
200 ccc ddd
How can I make the first table to the format of second table.
Thanks in advance for the help.
Thanks,
Trav
trav1
|
|
|
|
|
|
i am trying to connect to MS Access database using DSN and i am getting following error :
Microsoft OLE DB Provider for ODBC Drivers Error '80004005'
and
SQL Set Connection Attr failed
|
|
|
|
|
Ok, what language are you using?
Whats the code where your connecting?
Whats the connection string?
|
|
|
|
|
i am using VBScript
i am getting error at line where i have written
rs.open "DSNname"
|
|
|
|
|
Post all your code. No one can assist you unless you show us what you have done.
Paul Marfleet
|
|
|
|
|
Hi,
I am need of information regarding Database status during Database Refresh operation in sql server 2000.
The application uses 2 DB’s say db1 and db2. The db1 (read-write) and db2 (read only) resides in the server1. The db2 resides in server2 also.
Requirement: When the db2 in server1 is down i.e., when the DB refreshes, it throws an error and the application will not be able to access the DB tables. In that case, we should connect to the server2 and access db2.
Problem: In order to switch from server1 to server2, first we should check the status of db2 during refresh in server1.
Error during DB refresh:
Msg 927, Level 14, State 2, Line 1
Database 'Database_name' cannot be opened. It is in the middle of a restore.
If we can find the status of DB during refresh operation, this scenario can be handled and can be switched to server2.
Thanks,
Sarayu
Sarayu
|
|
|
|
|
Have you figured this out yet, or still working on it?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I have a table it has a name field and a rank field
Sample data
Name Rank
Amit 0
Ashish 1
Vikky 2
lalit 0
I want result back as follows
Ashish 1
Vikky 2
Amit 0
lalit 0
I want first data should be sorted on rank in ascending and than on name in ascending order for rows having rank 0
Is this feasible if yes how
|
|
|
|
|
For SQL-Server:
select Name, Rank from MyTable
order by case when Rank = 0 then 'B' else 'A' end, Name Other databases allow similar techniques.
Regards
Andy
|
|
|
|
|
Hi Ashish,
Use the below code,
create table temp#
(
assessmentid int,
appid int
)
insert into temp# select name,rank from table where rank <> 0 order by rank asc
insert into temp# select name,rank from table where rank = 0 order by name asc
select * from temp#
This will give you the output.
Regards,
Sarayu
|
|
|
|
|
Hi All
I have developed an application in which I use SQL Express 2005
All of my reports in the application are linked to database via instance name [MachineName]\OPMS
Now I want his SQL Express 2005, so that while deployment of the application, SQL Express 2005 create an instance name "OPMS" instead of SQLExpress which is infact default instance name.
Can any one please help me because I am in trouble
Regards
Muhammad Sharjeel Ahsan
|
|
|
|
|
First of all, do not hard code connection string. Secondly, you can install another version of SQLExpress with a different instance name.
Farhan Noor Qureshi
|
|
|
|
|
The connection string is no hard coded, I have placed connection string in App.Config so that user may change that string after deployment, but the problem is that Crystal report hard code the instance name of database
E.g. .\SQL2005
After deployment and during running of the application, crytal report search for that "SQL2005" So I want the SQL Express to have a user defined instance name which I first assign to All of report (under push or pull process) and then define that insance name to somewhere so that during installation of my application, tha instance name created while insallation of SQL Express, Please note that default insance name is SQLExpress but I do not need that name at all.
More over, if I install SQL Express directly then it asks for instance name but while installing as a pre requisite, it create SQLExpress.
Please reply asap
Regards
|
|
|
|
|
Hi,
I have deleted 100 rows from a SQL Database.I haven't database backup.
Is there a way to recover the DB?
Thanks in advance.
|
|
|
|