Database

Re: How to connect remote sql server(within LAN) using ADO?

21-Mar-10 20:55

Oh, I use my computer as sql server, the code which useing "Driver={SQL Server};Server=192.168.1.100;Address=192.168.1.100,1433;Network=DBMSSOCN;User ID=sa;PWD=123456;Database=DaDian2008" works well on my own computer, but doesn't on another computer.

Mycroft Holmes21-Mar-10 21:02

Mycroft Holmes

21-Mar-10 21:02

kcynic wrote:
192.168.1.100

This is a local IP, the other machine is looking for SQL on their machine, not yours. You might try replacing that with a machine name or find out your lan IP. I am presuming you want to use your machine as the SQL server.

Never underestimate the power of human stupidity
RAH

Re: How to connect remote sql server(within LAN) using ADO?

kcynic21-Mar-10 21:15

Re: How to connect remote sql server(within LAN) using ADO?

21-Mar-10 21:15

But, 192.168.1.100 is the real local IP of mine and i do want to use my machine as the SQL server. You meant, I should replace Server=192.168.1.100 with Server=MyPcName? But, if so, to a client witch knew its server ip only, how should it configure this argument? Unfortunely, if it's not a practice but a C/S product, one of its user is a orgnization with a plenty of members,they all need to install the client program, so they configure the Server argument with their server's correct name. And for another group, they have to do the similar work! In another words, the installation can't be done automately! right?

Mycroft Holmes22-Mar-10 0:18

Mycroft Holmes

22-Mar-10 0:18

You need to define your requirements

Are you on a LAN with all the clients
How many clients
Is you machine the dev box
Do you have a UAT server or even a database
Do you have a production server/ddatabase
Do you have an IT department, b/c if you are it you are in deep do do.

Never underestimate the power of human stupidity
RAH

Re: How to connect remote sql server(within LAN) using ADO?

kcynic22-Mar-10 1:38

Re: How to connect remote sql server(within LAN) using ADO?

22-Mar-10 1:38

Oh, there are the answers:

1. All clients within a LAN.
2. My pc is a dev box
3. I have MS SQL Server 2005 installed.
4. Of course no product. I just took that as a example
5. Im not a web developer, so b/s has no help for me.

No matter how many clients would be, i just only want to know how. Thanks

T210224-Mar-10 19:23

T2102

24-Mar-10 19:23

Depending on whether you use C++ or VB, the connection string is slightly different. Look at connectionstrings.com . Also, you should first make sure that you can connect with C:\WINDOWS\system32\odbcad32.exe before playing with your code.

Re: How to connect remote sql server(within LAN) using ADO?

kcynic25-Mar-10 15:31

Re: How to connect remote sql server(within LAN) using ADO?

25-Mar-10 15:31

Oh, thanks for you reply, i think i got the answer from that page.

Аslam Iqbal26-Mar-10 10:15

Аslam Iqbal

26-Mar-10 10:15

Identify what is your problem. Is that Network problem or SQL Server problem or both?
You are using LAN so test these:
1. Try ping command to/from server & clients’ PCs. (y/n?)
2. Or try file or printer sharing among PCs.
If you failed all above tests, you have Network problem. Otherwise you are facing the problem to connect with SQL Server.
All Mycroft Holmes’s discussions are about Network problem without being sure what the problem is.

I’m waiting to know what your problem is.

Re: How to connect remote sql server(within LAN) using ADO?

Elangovan Ayyandurai19-Dec-10 19:34

Elangovan Ayyandurai

19-Dec-10 19:34

Hi... i dont have any network problem but still i'm not able to connect to MS SQL Server which is in another system of my LAN.... Please provide me what to chect wit SQl server and Connection string... pLZZZZZZZZ.......

i luv forums...

Mike65432121-Mar-10 17:00

Mike654321

21-Mar-10 17:00

really gives us novices a great place to post our beginner questions:

SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1;

I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts?

Thanks again!

Mycroft Holmes21-Mar-10 20:41

Mycroft Holmes

21-Mar-10 20:41

Not based on that query. If the booktitle field is in a join or a where clause then it may need an index. Take a look at the execution plan and see where the main cost is, if it says table scan then an index will help.

Never underestimate the power of human stupidity
RAH

Mike65432121-Mar-10 20:47

Mike654321

21-Mar-10 20:47

I am not joining on the BookTitle field...I just wasn't sure if Count needed BookTitle to be Indexed when it aggregates the count command. I will leave BookTitle un-indexed, thanks!

Jörgen Andersson21-Mar-10 21:48

Jörgen Andersson

21-Mar-10 21:48

You could in theory get a faster query if you make a composite index on (IsFiction, BookTitle) and booktitle are having many duplicates.
But this is improbable in this case as booktitles are fairly unique, and you will have extra overhead on the inserts and updates.

"When did ignorance become a point of view" - Dilbert

i.j.russell23-Mar-10 0:20

i.j.russell

23-Mar-10 0:20

It is normally good practice to have indexes on fields used in where clauses. However, if IsFiction is a bit field, it will have low selectivity (a measurement of how unique each value is) which will mean that it is highly unlikely that the query optimizer would consider using the index. You are much more likely to find that the optimizer is using a clustered index scan or a table scan.

Mike65432123-Mar-10 8:42

Mike654321

23-Mar-10 8:42

maybe in need 2 go back to the basics...can anyone give me a link or two of a basic tutorial that talks about optimization...i always put indexes on Where or Order By fields if I am doing joins on the Where, and if I think the tables are going to have a huge number of records.

also, a simple tutorial link about the Query Optimizer and how to use it would be great too.

Thanks!

HELP: I can't verify text column is empty

i.j.russell23-Mar-10 23:29

i.j.russell

23-Mar-10 23:29

What you are doing is generally a sensible approach but you need to view the Actual Execution Plan that Sql Server generates to see if they are being used by the Query Optimizer.

There is a free pdf download from Red-Gate[^] that is very good.

http://www.simple-talk.com/books/sql-books/sql-server-execution-plans/[^]

If you are willing to spend some money, then buy this book.

SQL Server 2008 Query Performance Tuning Distilled[^]

I can't recommend this book highly enough. Although it is targeted at Sql2008, it's still very applicable to Sql2005 as well.

Curious 200921-Mar-10 12:07

Curious 2009

21-Mar-10 12:07

I am using VB 2008 & MS Access database

I have a table WebRateList with 2 columns ResFrom and ResTo defined as DateTime, another column WebID defined as Text (allow Null)

When I search:

strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "#"

Then it showed "It is in this range!"

But if I scan the same row with column WebID must be emptied, it didn't get in (infact the WebID is empty)

strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = NULL"
da = New OleDbDataAdapter(strSQL, dbConn)
dt = New DataTable
da.Fill(dt)

If (dt.Rows.Count() > 0) Then
    MsgBox("It is in this range!")
End If

Even I defined the following the result is the same

strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = '' "

Anyone can help? D'Oh! | :doh:

Re: HELP: I can't verify text column is empty

i.j.russell21-Mar-10 12:24

i.j.russell

21-Mar-10 12:24

You can't use = NULL, you must use IS NULL.

Re: HELP: I can't verify text column is empty

Curious 200921-Mar-10 12:45

Curious 2009

21-Mar-10 12:45

It worked!

Thanks for a very quick help Smile | :)

SQL update query

Mattzimmerer21-Mar-10 10:42

Mattzimmerer

21-Mar-10 10:42

This one should be easy OMG | :OMG:

...

I cant get a simple parametrized update query to function at all...

This is a simplified version of what I am trying to do...

UPDATE TheDatabase

SET ([Vehicle #] = @NewVehNo)

WHERE ([Vehicle #] = @OldVehNO)

I dont enderstand why this is wrong... I suck at SQL lol...

from MSDN i get the format:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Luc Pattyn21-Mar-10 10:53

Luc Pattyn

21-Mar-10 10:53

UPDATE targets a table, not a database.

suggestion: stop using those field names full of special characters, and get rid of those square brackets then.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

Don Burton21-Mar-10 11:17

Don Burton

21-Mar-10 11:17

Get rid of the parens. Such as:

UPDATE Thetable
SET [Vehicle #] = @NewVehNo
WHERE [Vehicle #] = @OldVehNO

and I assume you are updating a table - Yes?

Mattzimmerer21-Mar-10 11:21

Mattzimmerer

21-Mar-10 11:21

wait yes... i replaced that for some reason... i am updating a table

UPDATE [dbo].[Pickups & Cars] 

SET ([Vehicle #] = @Vehicle_NO)

WHERE ([Vehicle #] = @Vehicle_NO)

this doesnt work...

Don Burton21-Mar-10 11:26

Don Burton

21-Mar-10 11:26

Get rid of the parens:

UPDATE [dbo].[Pickups & Cars] SET [Vehicle #] = @Vehicle_NO
WHERE [Vehicle #] = @Vehicle_NO

Have tried the SP in Query Analyzer?