Bind 2 tables from different database file.

Hi!

I have 2 databases. One is ASPNETDB.MDF and another is PPQ_Data.MDF
ASPNETDB.MDF is generated by ASP.net (VWD 2005) when I use Login control.
PPQ_Data.MDF is created by me. It contains menu and order information of my website.

I would like to connect this 2 databases together so that I can createa GridView table that binds 2 tables, one from ASPNETDB.MDF and anothertable from PPQ_Data.MDF. So, it is kind of displaying nested data.

Is there anybody out there that know how to connect 2 databases together?

Again, my purpose of connecting 2 databases together is to pull out a table from each database and bind the 2 tables together.

thanks in advanced for any advice or articles submitted.

You can use qualified database object name to refer to tables in different databases, and even from different servers (you have to make sure the app can connect to all referenced databse resources). Forexample (I suppose you database name to be c:\ASPNETDB.MDF and c:\PPQ_Data.MDF):

select * from [c:\ASPNETDB.MDF]..table1 , [c:\PPQ_Data.MDF]..table2

For more information, please see this article:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_4ox9.asp?frame=true

|||Ohhw, yeah, thanks for the reply. I almost forget about SQL server online book. thanks!

Best way to insert large amounts of data from a webform to SQL Server 2005

Hi

I have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).

What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.

Any ideas?
Thanks
Ed

Hi,

If you gonna insert or update in single table then use SQL Data adapter. But it needs to have primary or unique key to create update query via sql command builder otherwise its just insert all rows in your table.

If you want to update for above scenario there are two ways.

1. Create staging table in your database and insert your records using sqlbulkcopy class which can insert lakhs of data/s (i'm not sure abt amt), then create a stored procedure to update or insert rows from staging table to main table

2. You can write your update and insert command in sqldataadapter.sqlcommand property.

for example

create a table as testtable with id number and name as text

Sql query will be like this

update testtable
set name = @.namecolumn
Where id = @.idColumn
If @.@.Rowcount = 0
Insert into testtable (id,name) values(@.idColumn,@.nameColumn)

then add sql parameters(@.idcolumn,@.nameColumn) with your data table column names

then adapter will execute the query while you call update method

hope it helps

best way to get queries fast

hi

i have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.

thanx in advance

cheers

Hi,

It depends on type of database usage you have, if its an OLTP system then you can not use a lot of indexes as it will slow down insertion and updation, however in OLAP system you can use indexes wisely and this will help a lot.

Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml

Best way for generating a Sequenzenumber

Hi
I'm interested to know what the besrt way would be to create a sequenze
number.
How can I prevent that a number will be created twice? What are your
thoughts?
Thank's
MichelHow about using an IDENTITY column?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:usU97m5oHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm interested to know what the besrt way would be to create a sequenze
> number.
> How can I prevent that a number will be created twice? What are your
> thoughts?
> Thank's
> Michel
>|||The number should start from 0 and we should be able to change the number in
some years to 0 again.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:uIxOgr5oHHA.4132@.TK2MSFTNGP02.phx.gbl...
> How about using an IDENTITY column?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Michel" <michel_mueller@.bluewin.ch> wrote in message
> news:usU97m5oHHA.4424@.TK2MSFTNGP03.phx.gbl...
>|||Michael,
declare your column: MyKey int IDENTITY(0,1) and it will start at zero and
count by one.
TRUNCATE TABLE MyTable will clear all the data from the table and reset the
IDENTITY counter back to zero.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:%239QX245oHHA.4032@.TK2MSFTNGP02.phx.gbl...
> The number should start from 0 and we should be able to change the number
> in some years to 0 again.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
> Newsbeitrag news:uIxOgr5oHHA.4132@.TK2MSFTNGP02.phx.gbl...
>|||> The number should start from 0
IDENTITY can start at 0.

> and we should be able to change the number in some years to 0 again.
Do you mean in combination with deleting all of the existing data? If so,
then yes, you can truncate the table (which will also reseed the identity
back to 0) or you can delete manually (which you will need to do, and in a
certain order, if other tables reference this identity value through an FK
relationship) and then use DBCC CHECKIDENT with RESEED and/or drop the table
and re-create it.
If you need to keep the existing data, please explain how anyone can
distinguish between the first row with a value of 0 and a new row when you
start over.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006