|
hi all
i'm hoping i'm right since i've put a lot of time into building the c# scripts to generate the select statement...
SELECT Customers.Cust_Cd, Titles.Title_Description, Customers.Cust_Name, Customers.Cust_Surname, Customer_Types.Cust_Type_Description,
Customers.Cust_Birthdate, Staff.Staff_Name, HomeFax.CCI_Area_Cd + HomeFax.CCI_Number as HomeFaxNumber, HomeNo.CCI_Area_Cd + HomeNo.CCI_Number as HomeNumber, HomeEmail.CCI_Email as HomeEmail, Mobile.CCI_Area_Cd + Mobile.CCI_Number as CellNumber, WorkFax.CCI_Area_Cd + WorkFax.CCI_Number As WorkFaxNumber, WorkNo.CCI_Area_Cd + WorkNo.CCI_Number as WorkNumber, WorkEmail.CCI_Email as WorkEmail
FROM Customers LEFT OUTER JOIN
Titles ON Customers.Cust_Title_Cd = Titles.Title_Cd LEFT OUTER JOIN
Customer_Types ON Customers.Cust_Type_Cd = Customer_Types.Cust_Type_Cd LEFT OUTER JOIN
Staff ON Staff.Staff_Cd = Customers.Cust_Staff LEFT OUTER JOIN
Customer_Contact_Info HomeFax ON HomeFax.CCI_Cust_Cd = Customers.Cust_Cd
and HomeFax.cci_Contact_Type_Cd = 3
and HomeFax.CCI_Context = 'Home' left outer join
Customer_Contact_Info WorkFax on Customers.Cust_Cd = WorkFax.CCI_Cust_Cd
and WorkFax.cci_contact_type_cd=3
and WorkFax.cci_context='Work' left outer join
Customer_Contact_Info HomeNo on Customers.Cust_Cd = HomeNo.CCI_Cust_Cd
and HomeNo.cci_contact_type_cd=2
and HomeNo.cci_context='Home' left outer join
Customer_Contact_Info WorkNo on Customers.Cust_Cd = WorkNo.CCI_Cust_Cd
and WorkNo.cci_contact_type_cd=2
and WorkNo.cci_context='Work' left outer join
Customer_Contact_Info Mobile on Customers.Cust_Cd = Mobile.CCI_Cust_Cd
and Mobile.cci_contact_type_cd=2
and Mobile.cci_context='Mobile' left outer join
Customer_Contact_Info HomeEmail on Customers.Cust_Cd = HomeEmail.CCI_Cust_Cd
and HomeEmail.cci_contact_type_cd=1
and HomeEmail.cci_context='Home' left outer join
Customer_Contact_Info WorkEmail on Customers.Cust_Cd = WorkEmail.CCI_Cust_Cd
and WorkEmail.cci_contact_type_cd=1
and WorkEmail.cci_context='Work'
Where Customers.Cust_wantEmail = '1' and(staff.Staff_name = 'rene' or staff.Staff_Name = 'maryke')
go
Am i right to say that i can't speed up the select statement any more...?
He who laughs last...
|
|
|
|
|
|
Yes, thanks.
i'm not complaining about the speed that it is currantly running at, i just wanted to know if it would be faster if i used the UNION <in some="" way="" or="" another=""> or if i'm getting the full speed by just using the select with the joins as it is. I'm not that familiar with the UNION statement. But from what i can make out is that it is more than one select statement, does that mean that the UNION might even slow the select statement down?
i don't know...
He who laughs last...
|
|
|
|
|
Why don't you build it by various methods then run it in Query Analyser and look at the Execution Plan.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Hi
I am Unable to access database
such as SqlServer for an application.
please get me an Procedure or Code
OK...
Bye.........
G.Nagaraju<b>
G.Nagaraju
|
|
|
|
|
|
i had combobox that binding to sqldataadapter so when i choose item form combobox(selectedindexchange event) another adapter are fire and fill table in data set then i update the second adapter so the data in the combobox may have to be changed so i want to make the data in the combobox uptodate when i try to clear sqladapter1 that fill combobox and fill it again an error happened taht the second adapter have parameter and didn't find value to assign to that parameter (adapter 2 was take the paramtere from combobox) do i try to before clear adapter1 to put combobx item in var to use to fill the second adapter but the error still occurred what i do to reslove that error
thanks.................
ma_refay
|
|
|
|
|
i want to know the order to reterive the machine name and the instance of sql server name to use in my connection to create general connection string that automatically put theses items in connection string so i mustn't change connection string when run appliction on another machine
ma_refay
|
|
|
|
|
|
Fellas,
How are you all?
Slight problem, I am developing a huge corporate's sales tracking application, I have decided for there to be a central SQL Server at the HQ and MSDE at branches across the country.
What is the quickest way to allow sales done at branches to be replicated across to the HQ. I think I am doing it the long way in manually checking if record exists and inserting if it doesnt for every table which will be in a dataset object.
Info will also move from HQ down to branches because the db structure is in such a way that all Sales Items can only be added at HQ and downloaded to office as per allocation. I have got a sales table that tracks sale info, a sale items table that tracks the actual items by id of the item which is linked to the items database.
How do I quickly replicate across dbs?
|
|
|
|
|
Do some research on Merge replication.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
sql server 2005
How do I delete all data of a specific column
Thank you.
|
|
|
|
|
Hi Candy,
Try using an update query:
"UPDATE targetTable SET targetTable.targetColumn = NULL"
Where targetTable is the table that contains the column, targetColumn which is the column you want to delete the data from.
Jim
|
|
|
|
|
Oh yah! Thank you so much.  :-> 
|
|
|
|
|
You can't get there from here...I always found that statement ironic. You hear it a lot when asking directions. It is like the problem I am having in creating a new Access database from ADO.NET programmatically.
First, you need a connection. This connection needs a connection string. The connection string has to have the path of the new database or it gripes about no Data Source. But when you put the new database name and path connection string it complains that the Data Source does not exist.
How do you do this? Any gurus know how to do this?
My plan was to:
OdbcConnection myConn = new OdbcConnection("Provider=blah..blah");
OdbcCommand myCommand = new OdbcCommand("CREATE DATABASE MyDbPath", myConn);
myComm.Open();
myCommand.ExecuteNonQuery();
...
How do you get there from here?
Thanks in advance!
-- modified at 22:48 Monday 19th June, 2006
|
|
|
|
|
Intriguing dilemma. I searched a little, and only came up with this:
http://support.microsoft.com/kb/317881/EN-US/[^]
The gist of it is that you need to use Microsoft ADO Ext. 2.7 for DDL and Security.
Build an Access Database
Open a new Visual C# .NET console application.
In Solution Explorer, right-click the References node and select Add Reference.
On the COM tab, select Microsoft ADO Ext. 2.7 for DDL and Security, click Select to add it to the Selected Components, and then click OK.
Delete all of the code from the code window for Class1.cs.
Paste the following code into the code window:
using System;
using ADOX;
namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\AccessDB\\NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
Console.WriteLine("Database Created Successfully");
cat = null;
}
}
}
Change the path to the new .mdb file as appropriate, and then press F5 to build and run the project.
The new .mdb file will be created in Access 2000 (Jet 4.0) format.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks Eric.
That is a very inelegant solution. Looks like I am stuck with using it. Surprizing that there is not a better way. What if the end user does not have ADO 2.7 on his machine?
Great job finding that buddy! It confirms the problem and at least offers a solution, elegant or not.
Thanks again.
|
|
|
|
|
how to convert a string to integer of length 4?which function to use?
P.PaVaN KuMaR
|
|
|
|
|
select convert(int,ColumnName) from TableName
or
select cast(ColumnName as int) from TableName
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I would suggest to add the isnumeric function if you are not absolutely sure that the string ALWAYS contains valid ints:
select cast( case when isnumeric(ColumnName)=1 then ColumnName else NULL end as int ) from TableName
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
r.stropek wrote: select cast( case when isnumeric(ColumnName)=1 then ColumnName else NULL end as int ) from TableName
Makes sense. Thanks.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
how to insert a boolean variable into sql table?it needs any conversion?give one example.
P.PaVaN KuMaR
|
|
|
|
|
|
i want a datetime format as mm/dd/yyyy in dataset i am getting
AcquisitionDate="2005-05-05T00:00:00+05:30"
if i used convert(datetime,AcquisitionDate) function ,the sql is throwing error convertion failed to datetime as character string
any other way to convert this
guide me please
-
|
|
|
|
|
It is too simple..
try this out...
--> convert(char,getdate(),101) <--function
....
vivek
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
