|
Hi,
It seems that you are using named instance. In such a case you should check what is the port of that named instance in order to point your connection string correctly.
For example, if your port number is 1550 you should specify the connection string as follows:
Network Library=dbmssocn;Data Source=127.0.0.1,1550;...
Hope it helps,
Uri
|
|
|
|
|
Using SQL Server (2005), and performing an INNER JOIN, let's say...
SELECT * FROM Employee E INNER JOIN Address A ON E.EmployeeId=A.EmployeeId WHERE E.Department='Sales'
It has occurred to me to wonder; mightn't it be more efficient to write that as...
SELECT * FROM Employee E INNER JOIN Address A ON E.Department='Sales' AND
E.EmployeeId=A.EmployeeId
But then I think; surely the engine is smart enough to optimize something so simple if it makes a difference.
I've tried executing a few of my joins both ways, and there's little difference between the two forms.
Can anyone here tell me whether or not such an optimization is performed? Or worthwhile?
|
|
|
|
|
I'd write it this way instead
select e.*
from employee e
where e.department = 'Sales'
and exists ( select a.EmployeeId
from Address a
where e.EmployeeId = a.EmployeeId
)
;
I've always found using the 'exists' clause with a second query to be more performant than trying to use a join (even though logically they are equivalent). 
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Ah, right, but that doesn't perform the join.
|
|
|
|
|
The two statements should be the same. Check the Execution Plan (CTRL + M).
|
|
|
|
|
That only seems to show the indices and such involved, which are of course the same.
My interest is more concerned with how big a temporary table gets generated by the query.
The first form may generate a very large temporary table and then pare it down based on the WHERE clause,
whereas the second form should generate a much smaller temporary table to begin with.
Look at it this way: what would you do if asked, "make a list of all the addresses for all the employees and give me the ones for the sales personnel"? Wouldn't you simply ask only the sales personnel and call it good?
|
|
|
|
|
That is where the cost estimator comes in. The query optimizer will choose the same plan either way based on the cost. If the table statistics (or the lack thereof) show that it will be more effective to filter the results from the employee table first then join to addresses it will do that in both cases. However, if the number of rows in the address table is less than the number of employees in the sales department then the optimizer may decide to pull all the rows from the address table and use them to filter the results from the employee table and then apply the filter on department.
The question is simply: how will the query optimizer interpret the statement based on the data provided by the cost estimator? Unless you enforce join order by using a join hint, the query optimizer will choose the best join order for the plan based on the cost data. If you want to enforce join order, then you will effectively tie the optimizer's hands and bend it to your will.
But no matter what you do the temporary table from the employee table will only ever contain the rows from the sales department. The statement is only parsed in the order you write it, then it becomes a data structure used by the query optimizer which is why, in this case it doesn't matter weither your filter is contained w/in the WHERE clause or the JOIN clause.
If you use SET STATISTICS IO ON you will see that the logical IO is the same either way, combine this with the execution plan of both queries and that tells me that there is no difference between the two statements at all.
|
|
|
|
|
plz guys
i need a help
how to put sql server instances running on the local area network in a combo or a list in vb.net 2003 and not in 2005.
i can put them in 2005 but in 2003 i can't
and i need it badly
plz plz plz
-- modified at 6:52 Thursday 4th January, 2007
|
|
|
|
|
|
Hi
I am learing ADO at the moment have just created a simple application to display & update the data in access data base. I have used data connection wizard to connect to the database. I dragged the table on to the form from the datasource viewer to create a simple datagrid along with navigator control. But now the problem is that when i add the new data in the datagrid and try to save it using the default save button on the navigator window, The changes just dosent get saved. Can anyone tell me why?. I am using C# 2005 express edition. Below it the details code that is created automatically for save item click
private void addressDataBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
{
this.Validate();
this.addressDataBindingSource.EndEdit();
this.addressDataTableAdapter.Update(this.addressBookDataSet.AddressData);
}
Thanks
Irfan
I am still learning
|
|
|
|
|
Verify that the event handler has been registered.
You can either :
1) set a break point in the addressDataBindingNavigatorSaveItem_Click_1 method and make sure the method is being called
2) or, look at the properties of the save button in the designer window under events. Make sure the click event has the method name as the property value.
If you try #1 and the break point isn't hit, you'll need to try #2.
|
|
|
|
|
Hi
I have check both the point. They are fine. I have tried dubugging it to make sure the on click is called & it is. It goes through the update method fine but for some reason just dosent save it
I am still learning
|
|
|
|
|
Hi, did anyone found the way how to do bulk insert on Sql Mobile 2005? MS announced bcp as a part of SqlMobile2005 but i did not find bcp.exe and SQL command like BULK INSERT are not supported ;(
|
|
|
|
|
Hello All
I need to create a SQL Query of follwing but this not work
can anyone help me to give actual query is has been work
my SQL Query is ---
select * from t_ItemPriceMaster Group BY c_DateForm Having ( ( max( c_DateForm )and c_SoftDelete='1')";
thanks for your time
|
|
|
|
|
Everything you are selecting must be in either aggregate function and/or in GROUP BY clause
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
jaganil wrote: my SQL Query is ---
select * from t_ItemPriceMaster Group BY c_DateForm Having ( ( max( c_DateForm )and c_SoftDelete='1')";
You have an extra "(" in the query
|
|
|
|
|
Hi Friends,
I am Vijayakumar, I imported one excel file into Ms-sql it is around 2MB. After imported it's grew upto 20 MB. We didn't do any modification in MS-sql. Can anybody please help me in this regard how it grew automatically.
With regards,
Vijay
|
|
|
|
|
What's expanded? The excel file. The size of the database? We need more information before we can give you a definitive answer.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have used the SQL Server Management Studio Express to set the default database and log file locations to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. I then use this string to try to create a database "If Not Exists(Select * From sys.databases Where Name ='DB001') CREATE DATABASE BD001 ON PRIMARY (NAME = DB001, FILENAME = 'DB001.mdf', SIZE = 2MB, MAXSIZE = 2GB, FILEGROWTH = 10%) LOG ON (NAME = DB001_Log, FILENAME = 'DB001.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)". However, I get the following exception: "CREATE DATABASE failed. Some file names listed could not be created. Check related errors." If I specify the entire path in the create string, then I can create the database without any problems. The problem I have is that when I deploy the application, I won't know the actual database path. I was under the impression that if I set the default database and log file locations using SQL Server Management Studio Express, then I don't need to specify the path in my create database string. Any help or explanation would be appreciated, thanks.
|
|
|
|
|
Hi All,
I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005)
i.e Add/drop a table, column, constraint etc. on a ongoing basis.
The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc.
So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database.
I have a couple of solutions in my mind but feel that they are not very efficient.
1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem.
2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there.
Can somebody please provide me some guidelines/tips on how to go ahead about this.
Thanks in advance.
PJ
|
|
|
|
|
hi,
where dataset tables stored ?
|
|
|
|
|
wrote: where dataset tables stored ?
Do you mean how do I get to the DataTables in a dataset? If so, DataSet exposes a Tables collection:
DataSet ds = GetDatasetFromDb();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0])
{
}
}
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi
I would like to use a C# windows application to determine the layout of a MS Access database. What I am trying to do is to specify an Access database by using an OpenFileDialog and then I want to retrieve a list of all the tables in the database. This will then populate a ComboBox. When a table name is selected in the ComboBox it must go and retrieve the data in the database and display it in a ListView. Displaying it is not a problem, but at the moment I hardcode the table name and when I want to look at another database with a different structure, I need to first find out what the table names are before I can retrieve the data.
Is there a way to dynamically get the database table names?
Thanks
Kobus
|
|
|
|
|
You could try something like
SELECT MSysObjects.Name<br />
FROM MSysObjects<br />
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1));<br />
Hope this helps out.
"That's no moon, it's a space station." - Obi-wan Kenobi
|
|
|
|
|
I use:
<br />
foreach <br />
( <br />
System.Data.DataRow dr <br />
in <br />
((System.Data.OleDb.OleDbConnection) this.dbc ).GetOleDbSchemaTable<br />
(<br />
System.Data.OleDb.OleDbSchemaGuid.Tables<br />
,<br />
new object[] { null , null , null , "TABLE" }<br />
).Rows <br />
)<br />
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
