Re: Remote Server - Database Discussion Boards

Mark J. Miller8-Mar-07 6:35

8-Mar-07 6:35

This requires a WAN setup of some sort. I'm no network admin, but a VLAN might work also. But either way there has to be a way for the account on your LAN to be trusted in the remote domain. You'll have to discuss it with the network admin in charge of the network where your SQL Server is located. Otherwise you're stuck using SQL Server Authentication.

How to get the Table Name and Total Row Numbers? [modified]

Khoramdin4-Mar-07 9:57

Khoramdin

4-Mar-07 9:57

Hello everyone,

I am trying to get the list of table in a Database and the total number of rows in each table. I have created the loop which loops and get the Table names. I have problem getting the total number of rows for each table in the Database. I successfully get the Table's name but once it comes to getting the total number of the row, I get problem. I am not sure if I can use the Odbc[0] once again to get the total number of the row since it is in use for the loop to get the Table's name.

My code is:

OdbcCom = new System.Data.Odbc.OdbcCommand("USE " + DataBaseName + ";", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES;", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
txtLog.AppendText("Tables inside " + DataBaseName + ":\r\n");
while (OdbcDR.Read())
{
txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
ListViewItem item1 = new ListViewItem(OdbcDR[0].ToString());

MessageBox.Show("Table name:" + OdbcDR[0].ToString());

// START COUNT
OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT COUNT(*) FROM " + OdbcDR[0].ToString() + ";", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();

// What should I put to get the return value "Total row number" in here?
item1.SubItems.Add("Need to add the total number of rows here!");
// END COUNT

listView1.Items.Add(item1);
}

Could you be kind enough to help me out on this one? Thank you very much and have a great day.

Khoramdin

-- modified at 16:16 Sunday 4th March, 2007

Re: How to get the Table Name and Total Row Numbers?

mr_lasseter4-Mar-07 11:11

mr_lasseter

4-Mar-07 11:11

You will need to create a new instance of the DataReader in the loop where you are trying to get the count. Or instead of getting the data reader you could call the ExecuteScalar function on the command.

Mike Lasseter

script DB

Mridang Agarwalla4-Mar-07 4:06

Mridang Agarwalla

4-Mar-07 4:06

hey guys,
i have quite a few SQL databases on my PC. I want to script all the databases into a text file. The script should contain both the data and the structure of the databases. How can i do this? I want to execute this process from a batch file. Is there a tool or something that i could use? I've been hunting for something like this for a long time but I haven't found anything. In MySQL tis is a relatively simple process which can be accomplished by using the mysqldump utility.

Re: script DB

Harini N K4-Mar-07 19:24

Harini N K

4-Mar-07 19:24

Hi
You can take a dump, use BCP for that and write it to a flat file. Then you can use command line prompt to export that flat file using BCP.

Check these link:

http://www.databasejournal.com/features/mssql/article.php/3391761[^]

http://www.paladn.com/bulk-copy-program.htm[^]

Rate this message. Thank you. Harini Smile | :)

Remote Server

alexrad4-Mar-07 3:11

alexrad

4-Mar-07 3:11

Hi 2 all,
Like use statment can select Db
is there any statment for Server
like use MyServer.Mydatabase

Re: Remote Server

Hesham Amin4-Mar-07 4:05

Hesham Amin

4-Mar-07 4:05

No But you can reference tables on other linked servers using fully qualified names:
Servername.DBName.ObjectOwner.ObjectName

Hesham A. Amin
blog

Re: Remote Server

Krish - KP4-Mar-07 21:40

Krish - KP

4-Mar-07 21:40

To fetch data from a different instance need ot link to the current instance

for which need to use sp_addserver / sp_addlinkedserver

Regards
KP

Re: Remote Server

alexrad5-Mar-07 1:29

alexrad

5-Mar-07 1:29

I am so f. tierd so plese be more specific if it is ok with u

Need help with tailoring a big sql statement

MasterShin3-Mar-07 11:52

MasterShin

3-Mar-07 11:52

Hi,

I have three tables with two relations.

First table is users, which just stores userids (autonumber), and a lastseen (text).
Second table is Names. Every user has names, right? So it's just rows of userid(long)/name(text).
Third table is IPs. Every user has a few ips, right? So again, it's just userid/ip.

The relations are just the ones I mentioned, a user has names and ips.

I have a name and an ip. I want to select a union from the IPs and Names.
The union needs to have only users that have the ip given in their ips, or - here's a tricky part - have an ip with first two octets the same as the given ip but also the name must appear in the user's names.

The idea is that given a name and ip, I want to select all previously entered users who are probably the same user.
But, after finding all these users, I also want to merge everything that I found to just one user. So all the ips I found should be updated to relate to the first user found, and the same for the names.

Any ideas?

I tried this: (given name is MasterShin and given ip is 204.15.0.0)

SELECT Players.userid, Names.name as [value], 1 as [type] FROM (Players RIGHT OUTER JOIN [Names] ON Players.userid = Names.userid) LEFT OUTER JOIN IPs ON Players.userid = IPs.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')
UNION
SELECT Players.userid, IPs.ip as [value], 2 as [type] FROM (Players RIGHT OUTER JOIN IPs ON Players.userid = IPs.userid) LEFT OUTER JOIN Names ON Players.userid = Names.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')

And after the select I can go over everything, save on the side what updates need to be done, and later do them.
But this big statement seems repetitive and not efficient...

Re: Need help with tailoring a big sql statement

WoutL3-Mar-07 12:07

WoutL

3-Mar-07 12:07

It is a little late for me, so I did not read your hole problem. Maybey tomorrow. But, youe have one table with userids and a table with usernames. Is it possible that a userid has more names? If not, why not combine those two tables to one?

Wout Louwers

Re: Need help with tailoring a big sql statement

MasterShin3-Mar-07 20:50

MasterShin

3-Mar-07 20:50

Yes. Users can have more than one name.

Re: Need help with tailoring a big sql statement

WoutL3-Mar-07 22:40

WoutL

3-Mar-07 22:40

I stil think it is strange that one user can have more names, and I still do not understand the exact problem. But maybe you can try this:

select u.UserID, <br />
[value] = case when i.ip = '204.15.0.0' then n.Name else i.ip end,<br />
[type] = case when i.ip = '204.15.0.0' then 1 else 2 end<br />
from Players u<br />
left join IPs i on u.userID = i.UserID<br />
right join n Names on u.UserID = n.UserID <br />
Where i.ip = '204.15.0.0' or (i.ip like '204.15.%' and n.Name = 'masterSin')

Wout Louwers

How should I deal with a table holding my time as Hour, Minute, and Second?

Khoramdin3-Mar-07 5:57

Khoramdin

3-Mar-07 5:57

Hello everyone,

I would like to create a table where I keep the record of time period which refrence to an event. The time variable is in the format of Hour, Minute, Second (Three Double Digits). How can I created a table to stroe these information and still be able to run a search quary?

I don't have a problem converting the time into only one number which would be second but I rather to keep the table in the same format of column for each of the hour, minute, and second.

Thank you very much for your help.

Re: How should I deal with a table holding my time as Hour, Minute, and Second?

Hesham Amin3-Mar-07 11:48

Hesham Amin

3-Mar-07 11:48

1-What is the database that you use ? MS SQL, Access, MySQL?
2-How will your searches look like?
3-Merging the 3 columns look a good idea to me and they will be easy to separate in UI or even in SQL.

Hesham A. Amin
blog

Re: How should I deal with a table holding my time as Hour, Minute, and Second?

alexrad4-Mar-07 4:32

alexrad

4-Mar-07 4:32

1.Create column with datetime datetype and name it MyDateTimeColumn
2. use getdate() to fill it
it will be like 28-02-2007 12:34:567
3.Use if it is helpful CONVERT(varchar(11),Mytable.MyDateTimeColumn,105)
This 105 is pamaretar and can be in range 100-114?
or from some two gidits number I think from 10 to ...try u will see

Sorry if this doesnt fit

SQL COMMAND NOT WORKING

govindkedia3-Mar-07 4:22

govindkedia

3-Mar-07 4:22

Hello group,

I am trying this command but i dont know its not working..
its not giving any error...but data is not getting inserted..i have tried to insert breakpoint..but program doesnt stop here..
about the databse structure..I am using SQL Server express edition
Site_ID is auto incremental in nature

Code is :

SqlDataSource siteDataSource = new SqlDataSource();
siteDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ToString();

siteDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
siteDataSource.InsertCommand = "INSERT INTO site(Site_Id,Site,Address,City,State,Zip,Country,Date,Institution_Id,Project_id) VALUES (@Site, @Address, @City, @State, @Zip, @Country, @Date, @Institution_Id, @Project_id)";

siteDataSource.InsertParameters.Add("Site_Id", siteidTextBox.ToString());
siteDataSource.InsertParameters.Add("Site", sitenameTextBox.Text);
siteDataSource.InsertParameters.Add("Address", addressTextBox.Text);
siteDataSource.InsertParameters.Add("City", cityTextBox.Text);
siteDataSource.InsertParameters.Add("State", stateTextBox.Text);
siteDataSource.InsertParameters.Add("Zip", zipTextBox.Text);
siteDataSource.InsertParameters.Add("Country", countryTextBox.Text);
siteDataSource.InsertParameters.Add("Date", dateTextBox.ToString());
siteDataSource.InsertParameters.Add("Institution_Id", irbDropDownList.SelectedValue.ToString());
siteDataSource.InsertParameters.Add("Project_id", projectTextBox.ToString());

Kindly Please Tell me where I could Make changes....

Govind

Re: SQL COMMAND NOT WORKING

Hesham Amin3-Mar-07 11:55

Hesham Amin

3-Mar-07 11:55

You did not call SqlDataSource.Insert[^] Method

Hesham A. Amin
blog

How to find no of slot empty of rack

mohd imran abdul aziz2-Mar-07 23:14

mohd imran abdul aziz

2-Mar-07 23:14

Dear All,

i want to find no of empty rack(two dimensional )

i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea

there is rackmst( where i define max_no_row and max_no_cols).

Please help

thanks

regards
imran khan

Re: How to find no of slot empty of rack

Hesham Amin3-Mar-07 3:02

Hesham Amin

3-Mar-07 3:02

Your question is not clear
Can you post the exact database structure and sample data ?

Hesham A. Amin
blog

What is N' ? Whats it use

Ankur.Bakliwal2-Mar-07 19:47

Ankur.Bakliwal

2-Mar-07 19:47

Hello

i've seen so many times N' in queries or SPs.
but i m still not able to understand this thing (N').
pls anybody can tell me
whats this?
why should we use it?
whats advantages of this thing (N')?

----------------------
Thanks,
Ankur Bakliwal

Re: What is N' ? Whats it use

Hesham Amin2-Mar-07 22:31

Hesham Amin

2-Mar-07 22:31

It's used to convert a string literal to wide character.
for example. If you have a column with nvarchar and you want to compare it with a string (where mycol= 'abc') you should make it like: (where mycol= N'abc')
you'll not notice that the first will not give the expected results unless you use a language other than English (Arabic strings for example).

Hesham A. Amin
blog

Re: What is N' ? Whats it use

Ankur.Bakliwal2-Mar-07 22:33

Ankur.Bakliwal

2-Mar-07 22:33

hey thanks for this information.......

Thanks,
Ankur Bakliwal

sql server user authentication

rrrriiizz2-Mar-07 17:36

rrrriiizz

2-Mar-07 17:36

hello all,

am using sqlserver 2000 as my database. my problem is am the DB administrator. i totally granting Permission to 10 user. i want to restrict 3 user can only access the DB at a time. All the others trying to enter, then it will shows that already 3 user's login u should try after some time...

Is It Possible? Please help me to solve this Problem.

Re: sql server user authentication

Krish - KP2-Mar-07 19:45

Krish - KP

2-Mar-07 19:45

Yes.
SQL server licensing can be set from control pannel.
by which max number of simultanious connections can be controlled.

Regards
KP

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