how can i connect to two databases?

hello,

i want to make a relation betwen one of my tables and the user tables (to take it's unique ID), if there isn't any methode to do that without using the two databases(ASPNETDB - automaticly created when a user registers, and MyData), how can i connect to both databases? here is my connection string, but what should i do?

<connectionStrings> <add name="SiteConnection" connectionString="Server=(local)\SqlExpress; Integrated Security = True; Database = MyData" providerName="System.Data.SqlClient" /> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyData.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /></connectionStrings>

thank you

Hello zuperboy90,

Maybe you should take a look at this post:-

Asp.net database created at for memebership logan

Cheers,

Eric

|||

is there any problem if i use the same databse(created by default) for users and my other things?...but still, it will be a very big mess there

|||

Hello zuperboy90,

I don't think there is any problem I supposed. If someone out there aware of any potential problem, please share your concerns with us.

Thank you.

Regards,

Eric

how can i connect tables.

Hi,

How do i connect tables so they are interrelated to each other. like the asp.net 2.0 membership tables have the "id"

and how can i use that?

i have multiple tables that are all connected.

like i have a table for profile. and images

profile with include their profile, while images will include there avatar.

now when i display there profile on page, i need to grab the data from both the profile table and avatar.

right now i've been using the "JOIN" statement. but is tehre any other way

only join is made in sql for getting information from interrelated tables in database.

use inner/outer as per your requirement.

thanks

vishal sharma

|||

Hello my friend,

The JOIN is the "de-facto" way of getting related data and saves you from having to do 2 or more separate queries. Some info on joins: -

FROM profile INNER JOIN images ... - if profile does not have an image or vice versa, no records will be returned.

FROM profile LEFT OUTER JOIN images - if profile exists but image does not, a record will be returned with the image fields as NULL. Profile record must exist.

FROM profile RIGHT OUTER JOIN images - if image exists but profile does not, a record will be returned with the profile fields as NULL. Image record must exist.

Kind regards

Scotty

|||

masfenix:

right now i've been using the "JOIN" statement. but is tehre any other way

- Yeah, you can do a SubSelect if you are doing something simple like getting 1 value from 1 table instead of JOINing to that table.

Example:

SELECT
*,
(SELECT TOP 1 Blah FROM myOtherTable WHERE myOtherTable.UserID = myTable.UserID)
FROM
dbo.MyTable

|||

so is what is it?

is it not join?

|||

join is better option.

other thing you can do is

select tableA.field, tableB.field where tableA.Id = tableB.Id something like that.

thanks,

satish

|||

You can see an example of when you would use JOINing or SubSelects of what I posted above can be found in this article :http://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx

How Can I Completely Delete a CE Table?

I have an application created with VS 2005 and VB 2005 using a Compact Edition Database. I created some tables that I no longer use or want. How can I permanently delete them from the database?

Connect to the database a run .ExecuteNonQuery:

Code Snippet

DROP TABLE tablename

You may have to run

Code Snippet

ALTER TABLE tablename DROP CONSTRAINT constraint_name

first, if you have foreign key constraints.

How can I avoid using a CURSOR inside of another CURSOR

I am trying to avoid using a CURSOR inside of another CURSOR.

Here's what I have to do...

I have 3 tables:

Table1 has 500,00 rows

Table2 has 25 rows

Table3 is empty

For each row in Table1, I want to INSERT 25 rows (from Table2) into Table3.

Using a CURSOR inside of another CURSOR, my code looks like this...

Define and open CURSORA for TABLE1

FETCH the first row from TABLE1 using CURSORA

WHILE (@.@.FETCH_STATUS<>-1)

Define and open CURSORB for Table2

FETCH the first row from Table2 using CURSORB

WHILE (@.@.FETCH_STATUS<>-1)

INSERT a row into TABLE3 (using the data from Table1 and Table2)

FETCH the next row from Table2 USING CURSORB

CLOSE the CURSORB cursor.

FETCH the next row from Table1 USING CURSORA

CLOSE the CURSORA cursor.

Using this CURSOR inside of another CURSOR is taking way too long.

Is there an SQL technique that I can use to get around using multiple cursors?

TIA!!!

DLdfrd

Use a setbased operation, its hard to guess the solution if you don′t give us more information what you actually want to do.

HTH, jens Suessmeyer.

|||You will probably want to use a Cross Join query -- TableA Cross Join TableB. Make a select statement that returns the columns you desire, then turn it into an Insert/Select.|||

Hey anomulous,

Thanks for the advice...

A Cross Join works very well. The join still takes awhile but one of the tables had 1.7 million rows but the Cross Join is a real big improvement.

This week, I have learned Inner Joins and Left Outer Joins. Today, I learned about Cross Joins.

Thanks again...

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](

[ID] [int] IDENTITY(1,1) NOT NULL,

[KOLON1] [nchar](10) NULL,

[KOLON2] [nchar](10) NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

how can build two tabels together with ms sql query

Hello to all,

I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together.

I used this query:

select * from Ta where Ta.Id = @.ID union all select * from Tb where Tb.IdOfa = @.ID

but it doesn't work and the following error message comes

"All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "

Can someone help me?

Thanks

best Regards

pinsha

Please post some sample data from each table and the result you are expecting out of your query.|||

Hi Pinsha,

While using Union Operator you must have the equal number of columns in both query.

You have written Select * Query 1 Union Select * Query 2

Please check if the number of columns are same in both the queries. ( I mean in both the tables as they are used in queries )

Satya

|||

satya_tanwar:

Please check if the number of columns are same in both the queries. ( I mean in both the tables as they are used in queries )

And the columns should be ofsimilardatatype.

|||

Off Course Bro...

Satya

How am I protecting my Database from freign people at my network

as well particular tables in my database should be acceible only for users supply special U/N & PWD within my program

(Windows application - VB.NET)

thanks for referring

You better to have a security policy for your ENV. SQL Server follows two level security architecture. One at server level (LOGIN) and the other at Databaes level (User). You can restrict the permission on objects. you can give access to table(i would always prefer to create view or sp to access table instead of giving direct select permission on table) to individual user or role.

http://msdn2.microsoft.com/en-us/library/aa905172(SQL.80).aspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0522.mspx

Madhu

How a storied procedure can read a table in MS Access?

Hi,

We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.

I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.

I've found info on how to add one record at a time but I need a more of a batch process approach.

Can you point me in the right direction?

Thanks,

Mary

You could use OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) function for reading table in Ms Access:

Code Snippet

USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

Also you could use OPENQUERY (http://msdn2.microsoft.com/en-us/library/ms188427.aspx) function for running queries at other databses, but you need to configure linked server first.|||

Thanks, I haven't gotten it quite working yet....took me a bit to turn on Ad Hoc Queries. I'm getting a could not be bound error on the table that is in SQL. If you have any ideas on how to fix that, sure would appreciate it.

-Mary

|||Never mind, I just forgot to alais the table...duh|||

It would be better to use a SQL Linked Server to the Excel file and use that instead of Access. What Access does behind the scenes in terms of query generation and calls to SQL Server should be outlawed.

Once the Excel sheet is linked, you can have a proc designed to do what you need with the data.

How a storied procedure can read a table in MS Access?

Hi,

We currently use an access database to manipulate data in tables on a SQL server. The end user copies a list of ProjNo from a spreadsheet into an Access table. The Access table is used as the criteria for 5 Access queries. It is common that one of the queries does not get run.

I figured I would create a Storied Procedure on the SQL server, call the storied procedure from MS Access using the MS Access table as the criteria to run the Storied Procedure. I do not know how to provide the Storied Procedure the ProjNo in the MS Access table. The number of ProjNo records in that table can vary.

I've found info on how to add one record at a time but I need a more of a batch process approach.

Can you point me in the right direction?

Thanks,

Mary

You could use OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) function for reading table in Ms Access:

Code Snippet

USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO

Also you could use OPENQUERY (http://msdn2.microsoft.com/en-us/library/ms188427.aspx) function for running queries at other databses, but you need to configure linked server first.|||

Thanks, I haven't gotten it quite working yet....took me a bit to turn on Ad Hoc Queries. I'm getting a could not be bound error on the table that is in SQL. If you have any ideas on how to fix that, sure would appreciate it.

-Mary

|||Never mind, I just forgot to alais the table...duh|||

It would be better to use a SQL Linked Server to the Excel file and use that instead of Access. What Access does behind the scenes in terms of query generation and calls to SQL Server should be outlawed.

Once the Excel sheet is linked, you can have a proc designed to do what you need with the data.

how 2 know the most requested tables

Is there a way to know the tables the mostly requested in a database
How please?

Because we want to copy indexes from DB2 to SQl tables, but since there re so many tables in the DB we want to start with the most requested tables by the applications

Thanks

Do you want to know from the DB2 database or the SQL Server database?

If you are talking about SQL Server 2005 you can check the DMFs sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
I want to know the most used tables in SQL 2005 database, but now I have no indexes defined yet. That s why I need to know the most used tables so that we can start creating indexes for these tables first.

The thing is we have to fix the problemes as soon possible, that s why we need to start with the most important tables , that s the mostly used tables so that we create their indexes first

Thanks a lot
|||

If you are on SP 2 (install it if you aren't), you really want to look at the Performance Dashboard Reports. That will help immensily in what you are trying to do. They are meant for troubleshooting performance problems. Looking at just the most used tables may not get you there as quickly as using these reports. You can drill through on missing indexes and look at the gains, impact you get from different indexes. The index recommedations is what you are looking for anyway based on what you just posted. In addition, the problems you are having may be more than just indexes and these reports will help with that. Check the following article and link to the reports:

http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx

-Sue

hot to rebuidl indexes

Hi,

I want to know how can I rebuild all indexes in all tables using T-SQL?

Thanks..

Hello Jassim,

This script will reindex all tables in a database. Just change the database name in the first line...

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @.TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Hope this helps! A full discussion of rebuilding indexes can be found here|||

Another command would be:

Code Snippet

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sys.sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2005

OR

use <dbname>

go

select 'DBCC DBREINDEX('''+object_name(id)+''','''',<sampling rate>)' from sysindexes where xtype = 'U' and indid in (1,0) -- For SQL Server 2000

The above commands would generate the T-SQL commands to re-index the tables of the database. You might want to use DBCC INDEXDEFRAG as this is an online operatio and DBREINDEX is an offline operation.

Hosting DBO Nightmare going Live in 3 Days and counting

My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.

I've used a few sql scripts to change the owner using

Exec sp_changeobjectowner 'object', 'user'

and I've used

Grant Execute on object to user

These have changed ownership but the site still doesn't work

Does anyone know what to do to achieve this.

Any help greatly appreciated

Many Thanks

DuncanI hate to tell you, but our hosting operation does give our customers only DDLAdmin rights...

...and they have NO (!) problem creating entries belonging to the dbo user.

It is trivial. Just make sure you create them for the dbo user in the first place (i.e. EXPLICIT).|||Thanks Thona, this seems to set all the tables up fine but when I run dotnetnuke, it has a index out of bound error, I think this is happening because it is not creating any records in the table