Database Discussion Boards

How to query all available database on the server

Member 258581919-Mar-07 21:46

19-Mar-07 21:46

Hi,

Say if all database on the server has the same structure, how do i query all of the databases available on the server? Can i do it in one single SQL query ?

The normal way to do this is to use multiple database selections. but this is too tedious (e.g: SELECT <column>.<db1> FROM <db1>,<db2>,... GROUP BY <column>.<db1> ORDER BY <column1> DESC). What happens if i have more then 30 databases of the same structure ?

Can anyone help me ?

Cheers Confused | :confused:

Sankai

Re: How to query all available database on the server

Tirthadip19-Mar-07 22:14

Tirthadip

19-Mar-07 22:14

i dont know what do u mean by same structure....do you mean you have some copies of a master database......then just construct the connection string properly with that provider name....you dont have to bother about the SQL Queries.....
keep your "connection string" in a module to act it as a global variable....

am i clear?? may be I didn't understand ur problem....

Tirtha

"A man can ride on your back only when it is bent....."

updating an sql database

Keshav V. Kamat19-Mar-07 17:22

Keshav V. Kamat

19-Mar-07 17:22

Hello all,

I am pretty new to sql databases. Please give me the syntax for update.
I need to confirm with what I am doing presently.

thanks in advance.

Keshav Kamat Smile | :)

India

Re: updating an sql database

N a v a n e e t h19-Mar-07 19:26

N a v a n e e t h

19-Mar-07 19:26

Keshav V. Kamat wrote:
Please give me the syntax for update.

What you want to update ? Table or DB ? Confused | :confused:

printf("Navaneeth!!")

www.w3hearts.com

Re: updating an sql database

Imran Khan Pathan19-Mar-07 19:28

Imran Khan Pathan

19-Mar-07 19:28

UPDATE [TABLENAME] SET FIELDNAME1='SETVALUE',FIELDNAME2='SETVALUE2',FIELDNAME3='SETVALUE3' WHERE ID='IDVALUE'

e.g

update employee set empname='Imran',city='Ahmedabad' where empid='1'

Best regards
Pathan

GOD HELP THOSE WHO HELP THEMSELF

SQLExpress usage advice please

Glen Harvy19-Mar-07 16:57

Glen Harvy

19-Mar-07 16:57

Hi,

I am new to SQLExpress (although not SQL) and am about to create a project in C# using it instead of MS Access.

I am a bit confused about some issues and would like advice/guidence on these issues please:

1. In the VS2005 IDE I can view the db, add tables etc manually which avoids the need of moving backwards and forwards with SQL Server Manager which is good.

As I have previously distributed my Access db in a subdirectory of the main program without problems, I would like to think I can do the same with the new .mdf file.

When coding however, the connection string would appear to require it to point to the orginal db in VS2005 Studio as follows:

string dbLocation = @"d:\vs2005 projects\mybookings\mybookings\mybookings.mdf";<br />
string connectionString = "Data Source=.\\SQLExpress;Integrated Security=True;Timeout=60;Initial Catalog = MyBookings;Application Name=SQLExpressTest;AttachDBFilename=" + dbLocation;

If I point the connection string to the copy of the db file in the subdirectory of the main program (like I do with MS Access) then I keep getting connection errors.

The question is - "How will this affect my program when I distribute it as obviously the connection string will point nowhere?"

2. I think I must be missing some basic understanding of use and deployment of SQLExpress enabled programs and would appreciate any references to any online sites you may be aware of. I've looked at the MSDN site and read the information there and no doubt I will eventually master it. However, the above problem concerns me now and I don't wish to start of wrong and have to go back and re-do my code. Unfortunately, almost everything I can find on this subject relates to ASP.Net and vb (which I find difficult to interpret particularly when I am trying to learn the subject - not the language).

TIA.

PS. PLease forgive the xpost but I think in this case it was warranted.

Glen Harvy

How to get the IP address of a user sitting behind a routher?

Khoramdin19-Mar-07 12:39

Khoramdin

19-Mar-07 12:39

Hello everyone,

I am working on an Windows Application writen in C# which gets connected to a remote Database server. I would like to be able to get the IP address of the users that get connected to the database server. In order to cheieve this, I am using the following code:

string strHostAddress = "";
IPAddress[] addresslist = Dns.GetHostAddresses(strHostAddress);
foreach (IPAddress theAddress in addresslist)
{
MessageBox.Show(theAddress.ToString());
}

Unfortunatly, for the users sitting behind a routher, I am only going to get their Enternal IP address such as "192.168.....".

I was wondering if it is possible to get the external IP address of the users?

Thank you very much and have a great day.

Khoramdin

Sql update problem

ali_reza_zareian19-Mar-07 11:34

ali_reza_zareian

19-Mar-07 11:34

I have program that have a realation to sql DataBank I have no problem when I add/delete but my dataBank don't updated!i use this code for update:
dataAdaptore.update(mydataSet);

Re: Sql update problem

mghiassi24-Mar-07 5:34

mghiassi

24-Mar-07 5:34

Hi You must set Update Rule property in Relationship of your database

Generate SQL Script for tables

dhananjayamurthy19-Mar-07 10:01

dhananjayamurthy

19-Mar-07 10:01

Please suggest me how to generate sql script for tables with data?

I hava some master tables contains data in it. I want to generate sql script with data. i.e., on run the script it should create the table structure and insert the data too.

Please suggest me...

Re: Generate SQL Script for tables

Colin Angus Mackay19-Mar-07 13:19

Colin Angus Mackay

19-Mar-07 13:19

dhananjayamurthy wrote:
I want to generate sql script with data. i.e., on run the script it should create the table structure and insert the data too.

You can query the data structure with the views on INFORMATION_SCHEMA. Although, that is quite complex task - I usually leave it to the Enterprise Manager to generate the creation scripts for me.

Data population scripts are slightly easier. You SELECT the data from the table, but concat' it all into one columns

SELECT 'INSERT INTO TableName(PK, Col1, Col2) VALUES ('+PK+', '+Col1+', '+Col2+')'
FROM TableName

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Re: Generate SQL Script for tables

Laxman Auti20-Mar-07 5:03

Laxman Auti

20-Mar-07 5:03

wrote:

You can use DTS package wizard to get the backup of table's backup.

Hope this helps you. Rose | [Rose]

Knock out 't' from can't,
You can if you think you can
Cool | :cool:

dataset.acceptchange method [modified]

manni_n19-Mar-07 7:58

manni_n

19-Mar-07 7:58

i am usind dataadapter and dataset objects to bind some data from the database in the textbox control. the code i m using is as follows:
textbox1.databindigs.add("text",dataset.tables(0),"Name")
This code is able to retrieve the field 'Name' from the database and puts it in this textbox. Now i want that if some change is made in this textbox,then that should be updated to the database table as well. for this purpose i m using dataset.acceptchange method. But this is not giving result.
please provide code if possible....
thanks..

-- modified at 14:40 Monday 19th March, 2007

How to script job in stored proc to run on dynamicly assigned server?

RainFox19-Mar-07 1:06

RainFox

19-Mar-07 1:06

Hi
I have a stored proc that creates a job with a job step that runs an integration services package.

It all works perfectly fine if the stored proc is on the server where the job needs to be created. It creates the job on the server where the stored proc is located and thus called from. What would I need to change to be able to run this stored proc on the server where it is located, but have it create the job on another server?

Thanks,

RainFox - cunning, with some cloudy clueless moments, yet cool and fresh Rose | [Rose]

Quick Index question

Rob Philpott18-Mar-07 23:09

Rob Philpott

18-Mar-07 23:09

Hey all,

We use primary keys to enforce uniqueness and thus provide a handle where you can get a particular row out of a table (and all the relationship stuff with other tables etc.) When the primary key is a compound key of several fields, what are the guidelines on their order within the primary key? I presume it is best to keep the more important ones (principle identifier, dates etc.) as the top fields in the key, and the lesser used ones lower down.

So, if I have a primary key which is say AssetId, [Date], DataSource, UserId in that order would my table benefit with a non-clustered index just on AssetId and [Date], or do you in effect get pretty much this through the primary key?

Hope that makes sense.

Regards,
Rob Philpott.

Re: Quick Index question

andyharman19-Mar-07 2:02

andyharman

19-Mar-07 2:02

Hi Rob

If your first index is on AssetId, Date, DataSource and UserId (in that order) then you don't really need a separate index on AssetId and Date because they are the leading columns in you primary key.

The conventional wisdom is that you should put your most unique columns at the front of indexes. I tend to recommend that you put the columns that are most commonly searched upon (which may be different).

If you also needed to frequently search on UserId and Date then you would consider using an additional index.

Note that (by default) SQL-Server uses a clustered index to enforce primary keys. This has the side-effect of appending your primary key columns to every non-clustered index that you create on the table. If you are using this primary key to join tables then you might want to consider using a separate identity column as the primary key on this table, and a separate non-clustered index on the four columns that you noted in your question.

Regards
Andy

Re: Quick Index question

Rob Philpott19-Mar-07 4:25

Rob Philpott

19-Mar-07 4:25

Thanks Andy,

Pretty much as I thought then, although your point about non-clustered indexes appending the primary key on is an interesting one. I had assumed that the non-clustered index would have some sort of 'pointer' which could read straight into one of the data files rather than a primary key value to lookup.

I would imagine this could make the indexes very large, especially as in my table there are 6 columns in the primary key. Worth thinking about!

Thanks again.

Regards,
Rob Philpott.

Re: Quick Index question

Mike Dimmick19-Mar-07 5:10

Mike Dimmick

19-Mar-07 5:10

For a table with a clustered index, the pointer into the base table is the clustered index columns. If you don't like this, don't use clustered indexes.

A clustered index means that the actual data rows are stored in the leaf levels of the index.

If the non-clustered indexes stored some physical location data as the pointer value, as they do for 'heaps' (tables with no clustered index), the indexes would have to be updated as rows moved within a page or between pages due to additional records being inserted. This wouldn't be easy.

Stability. What an interesting concept. -- Chris Maunder

how to perform pagging using stored processors

Imran Khan Pathan18-Mar-07 22:18

Imran Khan Pathan

18-Mar-07 22:18

Hi friends
i need to perform by using stored processors.
can ne1 tell me how do i do it?

thanks
Pathan

GOD HELP THOSE WHO HELP THEMSELF

Re: how to perform pagging using stored processors

Harini N K18-Mar-07 22:52

Harini N K

18-Mar-07 22:52

Hi
Do you mean that you want to pass current page, no. of records to display per page, total number of records etc....

This can be done.

Harini

Re: how to perform pagging using stored processors

Pete O'Hanlon18-Mar-07 23:52

Pete O'Hanlon

18-Mar-07 23:52

One approach is to pass in the id of the last row of the previous page and select rows based on this value. For instance, to retrieve 20 rows at a time:

SELECT TOP 20 ....
FROM Table1
WHERE MyID > @ID

To get the first batch, you would set @ID to 0.

Deja View - the feeling that you've seen this post before.

column length select query

playout18-Mar-07 20:31

playout

18-Mar-07 20:31

Hi,

I would like to retrieve specific region names, where a region code is less then 3 characters. I cant just do a normal select query, as some of the codes are more then 3 characters. Here is my sql query, but its not returning anything.

SELECT RegionName, RegionCode
FROM Region
WHERE DATALENGTH(RegionCode) < 3
ORDER BY RegionName

Re: column length select query

Ziyad Mohammad18-Mar-07 20:47

Ziyad Mohammad

18-Mar-07 20:47

Change query as below and try

SELECT RegionName, RegionCode
FROM Region
WHERE len(RegionCode) < 3
ORDER BY RegionName

Thanks,
Ziyad Mohammad [http://www.dotnetsoldier.blogspot.com/]

Re: column length select query

playout18-Mar-07 21:53

playout

18-Mar-07 21:53

Thanks alot. It worked. I never thought of LEN()...

installed in server and run from client

hamid_m17-Mar-07 23:21

hamid_m

17-Mar-07 23:21

hi everyone,
i need sample or tutorial of an application that work with ms access database (*.mdb) or sqlserver that installed in server and clients could run application with shortcut and work
i wanna create a networking application
many thanks

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.