Database Discussion Boards

Colin Angus Mackay1-Oct-07 6:32

1-Oct-07 6:32

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?

Upcoming FREE developer events:
* Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Re: SQL to get DataTypes

RichardBerry2-Oct-07 1:20

RichardBerry

2-Oct-07 1:20

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.

Re: SQL to get DataTypes

PIEBALDconsult28-Sep-07 5:42

PIEBALDconsult

28-Sep-07 5:42

I use ExecuteReader and then ask the DataReader for the DataTypes.

DataReader.GetSchemaTable()

or

DataReader.GetFieldType()

Re: SQL to get DataTypes

John-ph28-Sep-07 20:22

John-ph

28-Sep-07 20:22

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

Re: SQL to get DataTypes

RichardBerry30-Sep-07 21:36

RichardBerry

30-Sep-07 21:36

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?

Re: SQL to get DataTypes

John-ph30-Sep-07 22:51

John-ph

30-Sep-07 22:51

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

Re: SQL to get DataTypes

RichardBerry30-Sep-07 23:03

RichardBerry

30-Sep-07 23:03

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!!

Re: SQL to get DataTypes

John-ph30-Sep-07 23:21

John-ph

30-Sep-07 23:21

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

Making columns to rows in SQL

Trav128-Sep-07 2:28

Trav1

28-Sep-07 2:28

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

Re: Making columns to rows in SQL

Farhan Noor Qureshi28-Sep-07 9:59

Farhan Noor Qureshi

28-Sep-07 9:59

I can think of two approaches. First, if you are using SQL 2005, you can use PIVOT and UNPIVOT. Another approach could be to do this in SQL, C# or any other language you are using. See http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx[^]

Farhan Noor Qureshi

Microsoft OLE DB Error

paresh_joe28-Sep-07 2:04

paresh_joe

28-Sep-07 2:04

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

Re: Microsoft OLE DB Error

originSH28-Sep-07 2:11

originSH

28-Sep-07 2:11

Ok, what language are you using?

Whats the code where your connecting?

Whats the connection string?

Re: Microsoft OLE DB Error

paresh_joe28-Sep-07 2:40

paresh_joe

28-Sep-07 2:40

i am using VBScript

i am getting error at line where i have written

rs.open "DSNname"

Re: Microsoft OLE DB Error

pmarfleet28-Sep-07 2:46

pmarfleet

28-Sep-07 2:46

Post all your code. No one can assist you unless you show us what you have done.

Paul Marfleet

Database status during Database Refresh operation - Help required

sarayumadhavan28-Sep-07 1:20

sarayumadhavan

28-Sep-07 1:20

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

Re: Database status during Database Refresh operation - Help required

Paul Conrad2-Oct-07 10:23

Paul Conrad

2-Oct-07 10:23

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

SQL Query

Ashish_Sood28-Sep-07 0:55

Ashish_Sood

28-Sep-07 0:55

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

Re: SQL Query

andyharman28-Sep-07 1:57

andyharman

28-Sep-07 1:57

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

Re: SQL Query

sarayumadhavan28-Sep-07 2:03

sarayumadhavan

28-Sep-07 2:03

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

SQL Express Installation Problem While Deploy .NET Poject

ahsansharjeel27-Sep-07 21:55

ahsansharjeel

27-Sep-07 21:55

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

Re: SQL Express Installation Problem While Deploy .NET Poject

Farhan Noor Qureshi28-Sep-07 9:53

Farhan Noor Qureshi

28-Sep-07 9:53

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

Re: SQL Express Installation Problem While Deploy .NET Poject

ahsansharjeel28-Sep-07 17:31

ahsansharjeel

28-Sep-07 17:31

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

How can I recover the Database.?

pubududilena27-Sep-07 21:37

pubududilena

27-Sep-07 21:37

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.

Re: How can I recover the Database.?

pmarfleet27-Sep-07 21:59

pmarfleet

27-Sep-07 21:59

If you haven't got a backup of your database to restore from, you're not going to be able to retrieve your lost data.

Perhaps this will teach you the importance of taking regular backups of your database...

Paul Marfleet

MySQL to MsSQL Converter

totocaster27-Sep-07 21:24

totocaster

27-Sep-07 21:24

hi everyone Wink | ;)

plz tell me how to convert an MySQL *.sql file (CREATE TABLEs, INSERTs) to an MsSQL *.sql file, recommend software or something...

Tenx Wink | ;)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.