comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!
You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>
|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>
|||Sorry, replied to the wrong post - off by one error.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>

comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>|||Sorry, replied to the wrong post - off by one error.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>|||THANK YOU! Just wasn't getting my syntax right.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>

comparing data in two different databases

I am trying to learn how that I can write a select statement extracting data
from two different databases. Is this possible? I imagine that I can do it
with SSRS but I was looking for something very quick. For example, my client
wants to see a query of prices from 2006 prices versus 2007 prices which are
stored in different databases.
Thank you!You can query across databases on the same server by using
Database.dbo.tablename
If the databases reside on different SQL Servers you can use
Servername.Database.dbo.tablename (Linked Servers will be required first)
Ryan
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>I am trying to learn how that I can write a select statement extracting
>data from two different databases. Is this possible? I imagine that I can
>do it with SSRS but I was looking for something very quick. For example, my
>client wants to see a query of prices from 2006 prices versus 2007 prices
>which are stored in different databases.
> Thank you!
>|||Your pool won't run out of connections but it's possible that if you're
blowing out of the program without closing the connection you could have so
many connections open that SQL Express runs low on memory and slows down
enough so connection time out. It could also be that you're single stepping
through logic so it's taking an unnaturally long time for operations to
complete so they time out. I sometime bump up the timeouts in the
connections string if I'm going to be debugging.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>|||Sorry, replied to the wrong post - off by one error.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OTDDNtcWHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Your pool won't run out of connections but it's possible that if you're
> blowing out of the program without closing the connection you could have
> so many connections open that SQL Express runs low on memory and slows
> down enough so connection time out. It could also be that you're single
> stepping through logic so it's taking an unnaturally long time for
> operations to complete so they time out. I sometime bump up the timeouts
> in the connections string if I'm going to be debugging.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
>> You can query across databases on the same server by using
>> Database.dbo.tablename
>> If the databases reside on different SQL Servers you can use
>> Servername.Database.dbo.tablename (Linked Servers will be required first)
>>
>> --
>> Ryan
>> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
>> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>>
>|||THANK YOU! Just wasn't getting my syntax right.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%235yfjVcWHHA.5108@.TK2MSFTNGP06.phx.gbl...
> You can query across databases on the same server by using
> Database.dbo.tablename
> If the databases reside on different SQL Servers you can use
> Servername.Database.dbo.tablename (Linked Servers will be required first)
>
> --
> Ryan
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23L9xvRcWHHA.392@.TK2MSFTNGP06.phx.gbl...
>>I am trying to learn how that I can write a select statement extracting
>>data from two different databases. Is this possible? I imagine that I can
>>do it with SSRS but I was looking for something very quick. For example,
>>my client wants to see a query of prices from 2006 prices versus 2007
>>prices which are stored in different databases.
>> Thank you!
>

come on SQLdatareader....read....read....read...ok help.

OK, I'm using VS2003 and I'm having trouble. The page works perfectly when I created it with WebMatrix but I want to learn more about creating code behind pages and this page doesn't work. I think it has some things to do with Query builder but I can't seem to get change outside "please register". I have the table populated and it is not coming back with "login successful" or "password wrong" when I've entered correct information. Enclosed is what I've done in VS2003. Can you see where my error is? Any help would be greatly appreciated.
Thanks again.

Imports System.data.sqlclient
Imports System.Data
Public Class login2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "server=LAWORKSTATION;user id=sa;database=test;password=t3st"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"
Me.SqlCommand1.Connection = Me.SqlConnection1

End Sub
Protected WithEvents lblUsername As System.Web.UI.WebControls.Label
Protected WithEvents lblPassword As System.Web.UI.WebControls.Label
Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlConnection1.Open()
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
If dr("password").ToString = txtPassword.Text Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub
End ClassTake a look at this line here:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"

What you are asking here is to retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is "txtusername.text".

What you probably meant to ask is retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is the same as the value of txtUsername.Text.

Which should translate into command text like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

That should work. However, now you need to think about the possibility of a SQL injection attacks. Suppose somebody enters this is string as an email (single qoutes are significant):

'); DELETE Customer; SELECT ('

As you might've guessed, that would neatly zap your Customer table.

There are many ways to protect yourself from such an attack. Here's one possibility:

Create a function like this (pardon my VB.NET, it's rusty):

Function Tick(s As String)
Tick = s.Replace("'", "''")
End Function

And modify the command text assignment line like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & Tick(txtUsername.Text) & "')"

That would make sure that any attempts to close the tick marks in the statement would fail.

Anyways, happy coding!|||Better than trying to outsmart folks trying SQL Injection attacks,use parameters.|||OK, for testing purposes I did change it with querybuilder and this is what VS2003 enters in for the command:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = ' "" & txtUsername.text & "" ')"

It enters double "" by default...and it doesn't work.

I then manually entered you line precisely as typed above:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

I've also tried:
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE email = '" & txtUsername.Text & "'"

It still doesn't work. The response is always "Please register".

I've tested the connection and it works. The table data is correct when I enter in the right login and password but the prompt is nothing but "Please register". This page without the code behind works flawlessly. I'm stumped.|||Erm unless I'm reading this incorrectly isn't the result of txtUserName.text ALWAYS empty? You're populating the command string before anyone's typed in their name.

Try moving the ...CommandText = ... line just before the ExecuteReader|||Good catch, pkr!

He is right. Although we've fixed the one problem, there's still the other one present: InitializeComponent is called (and the CommandText property initialized) at the time the page is loading, which prevents the actual submitted value of txtUsername from ever being submitted.

As "douglas.reily" noted, time to call in parameters for help.

First, let's modify our query to this:

SELECT pass FROM Customer WHERE (email = @.email)

Then, just before you call ExecuteReader, add parameter to the command:

Me.SqlCommand1.Parameters.Add(New SqlParameters("@.email", textUsername.Text)

That should do it.|||I think you are reading it incorrectly. SQLdatareader runs SQLcommand1 which, in turn, runs the query for getting the login and password. SQLdatareader is run only when the user has clicked on the "submit" button and txtUsername.text is populated then. At least, I think this is how it happens.
I'm going to move to stored procedures and parameters instead but this is just bugging me that this page works perfectly fine without a code behind page but it has problems when I redid it with VS2003.|||OK, I've made some changes and added a parameter. In the "click" sub I've populated the parameter like this:

SqlCommand1.Parameters.Item("@.email").Value = txtUsername.Text

This seems to work. Thanks for all your help.|||Glad it's working. But out of interest does it still work in you put the ....value = txtUserName.Text... line back to it's original position. i.e. not in the same function as the button event?