Query To Another Database
Nov 15, 2005Dear all,
View 3 Replies
ADVERTISEMENT
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Mar 25, 2008Hi 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
Query To Give All Table Sizes On A Database (was Query Help)
Mar 9, 2006Hi,
Does anyone has query to give all table sizes on a database?
Appreciate your help.
Thanks
Import Wizard - Using Query For Remote Database To Compare Local Database
Apr 10, 2008I 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?
How To Query Database Roles In Some Database?
Mar 7, 2008How can I get all database roles from a specific database?
View 4 Replies View RelatedDatabase Query
Jun 7, 2008query 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
Database Query
Mar 16, 2004Is 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?
Two Database Query Help
Mar 25, 2006Can 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
How To Use Query To Get Sql Database Properties
Oct 2, 2006Hi,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 RelatedGet Values From Database Query
Jan 5, 2007I 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
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?
Query By Database Or Code
Nov 8, 2007I 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
Attach Database By Query
Apr 23, 2008Hi to all, Is there any way to attach a database using query.
View 1 Replies View RelatedHow Can I Get The Size Of The Database Query?
May 8, 2008hi,
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?
Cross-database Query From ASP.NET
Feb 7, 2005How 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 RelatedDatabase/Query Design Help
Oct 2, 2005I 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
How To Query Database Table
Oct 6, 2005hi, 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.
Multiple Database Query
Dec 4, 2000Good 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
Sql Query For Getting Information From Different Database
Feb 18, 2003I 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?
Multiple Database Query
May 17, 1999Hello,
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
Query Database Information
Oct 11, 2001Pardon 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
Database Properties Query
May 17, 2007I'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.
Run 1 Query Against All Views In Database
Jul 16, 2014In 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?
Query For Tables In A Database...
Feb 23, 2004Good 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
How To Know Database Size By Using Query ?
May 26, 2008Hi,
How to know database size by using Query ?
-- Regards
Prashant
Sql Query For Searching Database
Jun 14, 2006Dear 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
Sql Query On Oscommerce Database?
Nov 20, 2007Hi,
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
Is This Possible? Please Help (MS Access Query Of Sql Database)
Jul 20, 2005I 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 RelatedQuery 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
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
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.
Cross Database Query
Feb 18, 2008I 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;
How To Query SQL CE Database Schema
Sep 14, 2007What 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