Database Query

Mar 16, 2004

Is it possible to return the column names from the database using a SQL query,


what i need to do is


SELECT * FROM FEATURES WHERE 'VALUE' = 'YES'





i have a table which has a list of features and if they are selected i store the value yes, otherwise no . i want to be able to display a list of the features from the tables which have the value yes ! is this possible?

View 5 Replies


ADVERTISEMENT

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

Query To Give All Table Sizes On A Database (was Query Help)

Mar 9, 2006

Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks

View 2 Replies View Related

Import Wizard - Using Query For Remote Database To Compare Local Database

Apr 10, 2008

I am trying to use the Import Wizard to setup a daily job to import new records based on an ID field (PK). The source database is remote and a replica. I am inserting new records to update my table called the same thing. Both are SQL Native Client


Code Snippet
select *
from [CommWireless].[dbo].[iQclerk_SaleInvoicesAndProducts] as S1
join [IQ_REPLICA].[dbo].[iQclerk_SaleInvoicesAndProducts] as S2
on S1.SaleInvoiceID = S2.SaleInvoiceID
where S1.SaleInvoiceID > S2.SaleInvoiceID


When I parse the query, I keep getting an error message.

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name 'IQ_REPLICA.dbo.iQ_SaleInvoicesAndProducts'. (Microsoft SQL Native Client)

Anyone know an easy why to get this to work? Or should I add a create table to verify new records?

View 8 Replies View Related

How To Query Database Roles In Some Database?

Mar 7, 2008

How can I get all database roles from a specific database?

View 4 Replies View Related

Database Query

Jun 7, 2008

query to delete a single desired  record among 100  identical records in sql server
consider
             2     ravi
            2      ravi
           2       ravi
...............................                like that 100 records are there now how to delete 46th record from table

View 3 Replies View Related

Two Database Query Help

Mar 25, 2006

Can someone show me how to write a query, that will allow me to create one record, from two record fond in two other databases?
 
Thanks

View 2 Replies View Related

Query To Another Database

Nov 15, 2005

Dear all,

View 3 Replies View Related

How To Use Query To Get Sql Database Properties

Oct 2, 2006

Hi,I'm creating a User Interface to display Sql database Properties, but I cannot find the right query to retrieved the info on database properties. The status that I get is "ONLINE", but it should be "NORMAL". Cannot find the right query to get the date of last database backup, last transaction log backup, and maintenance plan, etc.. Please help me [:'(]  

View 2 Replies View Related

Get Values From Database Query

Jan 5, 2007

I have written the following lines myConnection = New MySqlConnection("server=" + dbServer + "; user id=" + dbUserID + "; password=" + dbPassword + "; database=" + dbName + "; pooling=false;")
strSQL = "SELECT * FROM user where type=1;" 
user table has name, tel, addr, id, type fieldsI would like to know how to use a string array to store the name in the result of strSQL?Thank you

View 1 Replies View Related

How To Query For NULL In A Database

May 31, 2007

 
  In my database, in a few tables there are NULL values in some of the columns. This is okay, but I need to know how to query for nulls. For example I tried the following query but it did not work:
select * from Employee where DateOfBirth =NULL
This did not work so I also tried the following:
select * from Employee where DateOfBirth ='NULL'
 
Neither of these worked. Can someone help me out?

View 1 Replies View Related

Query By Database Or Code

Nov 8, 2007

I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks

View 6 Replies View Related

Attach Database By Query

Apr 23, 2008

Hi to all,           Is there any way to attach a database using query. 

View 1 Replies View Related

How Can I Get The Size Of The Database Query?

May 8, 2008

hi,
How can I get the database size through query?
How can I get the Size of the binary file(image file ) inserted in to the Image Data type of the SQL datatable?  

View 7 Replies View Related

Cross-database Query From ASP.NET

Feb 7, 2005

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?

View 1 Replies View Related

Database/Query Design Help

Oct 2, 2005

I am designing an ASP.NET app that can be used to keep track of
attendance at office hours for a class.  The purpose of this is
that we need to know if a student is attending office hours by
different people (so that we can flag them as "in trouble").  I
don't know if I have chosen the best database design, and I'm lost as
to how to accomplish a query I need.

I have a table HoursAttendance that has the following design.

Column_Name   Data_Type   Length   Allow_Nulls
TA              
      char        
      4         
n
Date              
    smalldatetime 4     
    n
Start              
    smallint         
2          n
End              
     smallint     
    2          n
Student1            
bit              
  1          y
Student2            
bit              
  1          y
Student3            
bit              
  1          y
Student4            
bit              
  1          y
Student5            
bit              
  1          y

I chose to have the students as columns because the students don't
change, and then you add rows of office hours.  If students are
the rows, then you would be adding columns as the semester continued
which I thought was odder...?  I'm completely open to suggestions
on Database Design, because I really wasn't sure.

Ok, so now I need useful queries.  The one that I am stuck on (and
also the first one besides select * from HoursAttendance) is that I
want the names of Students who have attended more than x office
hours.  So I need something like

    select <column name> from HoursAttendance where count(sum(<column name>)) > x

Granted a better table design could help with this.  I'm
relatively new to design, so constructive criticism is desired please

View 7 Replies View Related

How To Query Database Table

Oct 6, 2005

hi,   i've got problem on how to query my two tables:Table statuslogFIELDS:           row1                       row2ActId                 :  1                             2ActDate              :   2005-9-19           2005-9-18PIN(employee) :   P120                     P120ProjCode        :  1234                       123ActCode         :   B                            IMap#             :   map1                   kd145RegHrs           :    0.5                       7.0OtHrs             :     0                        2.0Status(%)      :   20                         100
Table DalsDataNewFIELDS:           row1                       row2ID                 :  24                            25Date              :   2005-9-19           2005-9-18PIN(employee) :   P120                     P120ProjCode        :  1234                       123ActCode         :   B                            IActMedium     : W(PC)                    W(PC)Map#             :   map1                   kd145RegHrs           :    0.5                       7.0OtHrs             :     0                        2.0Flag               :    0                           1Approved by    :  P084                       P083
if you will notice some fields of my tables have same value. these are: Date,ProjectCode,ActCode,RegHrs,OtHours
what i would like my output(data) to be in my datagrid:ActId,ID,Date,ProjectCode,ActCode,ActMedium,RegHrs,OTHrs,Status,Flag,Approved by
i have this query in 1 table only."SELECT * FROM statuslog WHERE statuslog.Pin = '"+Session("user")+"' and statuslog.ActDate >= '"+DateFirst+"' and statuslog.ActDate <= '"+DateEnd+"' ORDER BY statuslog.ActDate DESC"
but what i would do now is to query the two tables and same where clause in the query above.

View 1 Replies View Related

Multiple Database Query

Dec 4, 2000

Good afternoon one and all,

I want to write a query that will affect data across two databases (on the same server). How would I do this?

TIA for all help

Gurmi

View 1 Replies View Related

Sql Query For Getting Information From Different Database

Feb 18, 2003

I want to write a sql query for information from a table in different database but on the same server.

what is the syntax.


select * from databse2.owner.table ??


i am in application1 database and i want to query from application2 database on the same server.

use application1
go

Select * from application2.dbo.tablename
go



Is this query correct?

View 1 Replies View Related

Multiple Database Query

May 17, 1999

Hello,

Can anyone tell me if SQL Server 6.5 has anything similiar to linked tables in MS Access? I need to query two tables simultaneously that reside in separate SQL databases. Is this possible, or must the tables reside in the same database?

For example, I have two databases db1 and db2. Table1 is in db1 and Table2 is in db2. I want to query something like this:

"SELECT * FROM table1 WHERE fieldname1 in (SELECT fieldname2 FROM db2.Table2 WHERE...)

Thanks,
Andrew

View 1 Replies View Related

Query Database Information

Oct 11, 2001

Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.

Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.

Thanks,
Mary Elizabeth
NW Natural

View 1 Replies View Related

Database Properties Query

May 17, 2007

I've been asked to provide some information about databases on an sql server.

I can use the following query to provide the names of all the databases:

select * from master.dbo.sysdatabases

What I'm also looking to provide is the current "size" of the database. I'm trying to find a table that I can link to that would provide the database size. I can manually find this number by right clicking on the database from enterprise manager, selecting "properties" and picking it up from the general tab.

This is sql server 2K...

Thanks.

View 2 Replies View Related

Run 1 Query Against All Views In Database

Jul 16, 2014

In SQL server 2008 how can I run the same query against each view in that database?

One database about 75 views.

Or even something like
Select 'yes' from view
Where shipping_status = 'delayed'

And return a list of view names so I know which views to query against.

I ran

Code:
select name from sysobjects where type = v

And got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?

View 1 Replies View Related

Query For Tables In A Database...

Feb 23, 2004

Good Morning

Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:

SELECT name
FROM sysobjects
WHERE (xtype = 'U')

Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?

Thanks,
Ed

View 6 Replies View Related

How To Know Database Size By Using Query ?

May 26, 2008

Hi,

How to know database size by using Query ?

-- Regards
Prashant

View 13 Replies View Related

Sql Query For Searching Database

Jun 14, 2006

Dear Sir,

I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.

Can you provide me a strong SQL Query for that. Or is there any other way of doing that.

Please help me.

Thanking You

Suraj

View 2 Replies View Related

Sql Query On Oscommerce Database?

Nov 20, 2007

Hi,

I have an oscommerce database.
I uploaded my categories and products in english language.
I would like to write an SQL query that will copy all my categories (only the categories) from english to my other language.

Could someone help me please to do that??

Thanks a lot for your kind attention !

Best Regards & Great day,
Nathali

View 5 Replies View Related

Is This Possible? Please Help (MS Access Query Of Sql Database)

Jul 20, 2005

I work for an organisation that uses a bespoke document imaging system, thedatabase of which is an MS sql server.We have MS Access and already use it for some querying of the database.The database comprises a large number of distinct cases, which aredifferentiated by case reference numbers, in one field (table?) of thedatabase. Each of these cases may have many documents associated with it,denoted by the reference number, and these documents may be "new", "pending"or "complete" shown in another data field.We need to know how many cases have work outstanding on them.Our problem is that our bespoke software will only count the number ofdocuments of each status, and not the cases.Is it possible to design an MS Access query which will count the number ofdifferent reference numbers which have any "new" documents associated, butwont count each case more than once?I am reasonably computer-savvy, I just don't know Access or SQL..If I know it is possible, I don't mind putting in the effort to find outhow. I just don't want to waste time barking up the wrong tree ;-)Of course any advice about how this would be achieved, such as pointers tothe right parts of the MS Access helpfiles, or to relevant websites would begreatly appreciated. Some quick code would be even better...Also, if there is any 3rd-party software which could easily do this, I needhelp discovering it...I have looked long and hard, but don't know enoughabout what I am looking for.Yours in hope..--anthonyberetPlease reply in the groups, as my Usenet email address is not working at themoment.

View 5 Replies View Related

Query Data From A Different Database

Sep 13, 2007



Greetings,

Is it possible to create a view that selects data from a different database, SQL 2005?

i.e. Server A.Database 1.Table 1, Can Server B.Database 2.View1 be based on Server A.Database 1.Table 1

I thought I browsed by a page a few days ago that gave an example. Unfotunately I did not bookmark it.

Thank you,
Charles

View 8 Replies View Related

Sql Query To Details About Database

Sep 11, 2007



Is there any query to list all tables,column name,datatype,size,total record in each tables for particular database
ex
Database:Northwind
Tables:categories,(categoryid varchar(50),Category name varchar(50)...............),1023kb ,25 records like this
i think there is no such a simple query...
Atleast tell How to get all column names and datatype of particular table and size of table

View 5 Replies View Related

Cannot Query Database With SSMSEE

Dec 6, 2007



hello,

I have installed win2003 R2 and SQL Express 2005 with SSMSEE SP2. I made test database and test table. When I try to select from database I get error message: unknown error "-l". But when I try do select with SQLCMD then I have no problem.
Someone knows what may by a problem.

View 7 Replies View Related

Cross Database Query

Feb 18, 2008

I have Two databases e.g. PDC,STB(has a table named "user" and fields of the table is "name","time_of_login").i want to create a stored procedure in PDC database which will insert data into STB's "user" table.

OR

how to run query from QueryAnalyzer by setting database from combobox at top as PDC and run select * from STB.user;

AS

in oracle we can connect via one user
and can select the tables of another user.
like
connect internal/oracle
select * from scott.emp;

View 4 Replies View Related

How To Query SQL CE Database Schema

Sep 14, 2007

What are some basic commands to query information about a CE database schema. I'd like to see my foreign keys etc. Where can I find this information in BOL?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved