|
Using subqueries (the SELECT statements you speak of) in INNER JOINS can be very useful. However, here it looks stupid because it is just doing a SELECT * FROM... and I guess this would be sub-optimal because it has to create an internal set of data for the subquery that contains every field and they may not all be used once the data gets into the main query.
It would be better to do:
INNER JOIN Schema_types ON Schema_types.Schema_typeID = Schemas.Schema_typeID
INNER JOIN Marketeers ON Marketeers.MarketeerID = Shipments.MarketeerID
If my understanding of the query optimiser is correct then this should also speed up your query.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
plz tell me as early as possible that how to bind a report with dataset of sql password protected database at runtime.
it is urgently required
|
|
|
|
|
can you try to be more specifc!
give us an example of what you are doing, a part of your code or somthing...
sony speed
|
|
|
|
|
Private Sub ShowReport()
Dim rpt As New CrystalReport1() 'The report you created
Dim dsReport As New Dataset1() 'The dataset that you created for this report
'We need a report object that is fully populated to display, so declare one
Dim objReport As Object
objReport = prepareReport(rpt, dsReport, "SELECT * FROM MyTable")
rpvReport.ReportSource = objReport
End Sub
'To get a report from Table
Public Overloads Function prepareReport(ByVal rpt As Object, ByVal dsReport As Object, ByVal tblName As String) As Object
Dim myConnection As SqlConnection
Dim MyCommand As New SqlClient.SqlCommand()
Dim MyDA As New SqlDataAdapter()
Try
myConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=northwind;")
MyCommand.Connection = myConnection
MyCommand.CommandText = tblName
MyCommand.CommandType = CommandType.Text
MyDA.SelectCommand = MyCommand
MyDA.Fill(dsReport, “MyTable”)
rpt.SetDataSource (dsReport)
Return rpt
Catch ReportErr As Exception
Reponse.write("Prepare Report Error:" & ReportErr.Message)
Finally
MyDA.Dispose()
MyCommand.Dispose()
myConnection.Dispose()
End Try
End Function
Things to do before Coding.
1.Create a dataset (named dataset1) which has to bind the
necessary table or view (in this case it is MyTable)in the integrated environment.
2.Create a Report (in this eg -CrystalReport1) and bind the report with the dataset you have created(Dataset1)
3.Drag and drop the necessary fields
4.Use the code
5.Ur Report is ready 
Things To Ensure:
Check whether you have 2 import statements in your vb
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Regards,
CVP
|
|
|
|
|
Hi all,
i want to prepare an installation package in microsoft SMS installer,
and i want to install the SQL Server 2000 Desktop Engine through that script.
how can i do that ? i read that we can install the MSDE through commandline, if so i can do that by using the script without knowing the user about the SQL Server 2000 Desktop Engine.. but how.??
from where i get the SQL Server 2000 Desktop Engine for that purpose , and how can i do that from command line..? is there any further constrains in that ..?
please replay
regards
renjith
programming in VB is like riding a kiddy bike, while programming in C++ is like driving a Formula 1 racing car
|
|
|
|
|
no one here to answer this..!!!
i found the solution...!!!
i think i will post an article regarding this..
thank you
programming in VB is like riding a kiddy bike, while programming in C++ is like driving a Formula 1 racing car
|
|
|
|
|
Download the MSDE2000A.EXE from microsoft.
You run thiw first, and it will decompress to C:\MSDERelA folder.
If you run it with /s it will not display a user interface.
Then you can run the Setup.Exe using some fo the arguments documented in the readme.
A word of caution, even though the documentaiton says that the targedir must end with a backslash, this is just not true 
You can read/process the log file to see how successful setup was, if the exit code from the process is 0.
|
|
|
|
|
What is the advantage of using procedures over sql queries?
Sumit Domyan
TeraByte Solutions
|
|
|
|
|
1. Security - underlying tables need not be given permisions
2. Precompiled cache - so execution is fast
3. Application need not be recompiled completely if any query needs to be changed
Bhaskara
|
|
|
|
|
4. Can cut way down on network traffic. Lengthy query code is already stored on the server. One small call can process a lot of tables with multiple transactions.
5. Cross table/db validation is easier/faster at the data store.
6. Stronger protection from bad parameters (sql injection).
7. Job security - less people know TSQL.
#2 isn't quite as true as it used to be. SQL Server has certainly improved its speed and caching ability over the years.
|
|
|
|
|
use of procedure decrease the trefic over network....as a result u get better speed.
store procedures are precompiled quries ..it help server to not compile again and again..it prevents the server resource overhead.
easy to call in code..instead of writing long quries.
easy to manage code...while haveing procedures instead of large quries.
easy to check quries error...either syntax or logical...it helps a lot to make quries error free....cool use and enjoy.![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Hey all. I'm having a problem getting a connection to a SQL Server to timeout. What is happening is I am using CDatabase::OpenEx (code below). If the computer is disconnected from the network, or the database server is down, the program just hangs forever. It never even times out after five or ten minutes and I have to manually end the process.
Here's what I'm using to connect:
CString DBCONNECT = "Driver={SQL Server};Connection Timeout=10;Server=DSC\\DAVEINSTANCE;Database=Master;Uid=sasd;Pwd=blah;"
CDatabase myDB;
myDB.SetLoginTimeout(10);
TRY {
myDB.OpenEx(DBCONNECT,CDatabase::noOdbcDialog);
.... If the server is there, it works great, connects instantly. If the server is not running, or the computer is not plugged into the network, the program just sits forever (it does not even time out after 5 or 10 minutes!).
I've tried without "Connection Timeout=10;" in the connection string, and only using myDB.SetLoginTimeout ... and vice versa ... without SetLoginTimeOut. I've also tried "Connect Timeout=10" Neither of these are working. I saw in the MSDN that some connections do not allow SetLoginTimeout, but I'm guessing there has to be a way to time out the connection for SQL Server instead of having the program hang on myDB.OpenEx().
Any help is appreciated!
|
|
|
|
|
Hi,
never posted anything with "very urgent" in the subject but this time I'm having a serious problem. I've got a customer which called me to tell me that his sql server doesn't start. I had a look and decided to reinstalled the server and it works fine now. The problem has been somewhere in the master table. I found this out when I copied the old data files (mdf, ldf) over the newly created ones and restarted the server. The sql service doesn't even start. Obviously, the master table is not in a good condition. Now the problem is, the client has got his bookholding in one database. Unfortunately, he doesn't have a backup, neither of the master table nor of the bookholding one (don't even ask). So all I have are his latest data files (say mybookholding.mdf and the corresponding ldf file + master.mdf, etc. But as mentioned, master.mdf is of little use). Question is now, how can I get his bookholding database up and running again if I only have the mdf/ldf files?
Thanks in advance!!
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hi Matthias,
Have you tried attaching the client's mybookholding.mdf file to a new sql server?
Get sql server running on a server.
Copy mybookholding.mdf and .ldf to the data folder
Use query analyzer: go into the master db on the new server and try this,
EXEC sp_attach_db @dbname = N'MyBookHolding',
@filename1 = N'c:\mssql7\data\mybookholding.mdf',
@filename2 = N'c:\mssql7\data\mybookholding.ldf'
Note: the code above is for Sql 7.0, but hopefully it'll get you started...
Just brainstorming,
Bill
|
|
|
|
|
Thanks for your posting,
I've found a microsoft issue which is closely related to the error 9004 which i received and is fixed with SP 3a. I updated and everything is fine now. Thanks anyway for your help.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Matthias Steinbart wrote:
the client has got his bookholding in one database. Unfortunately, he doesn't have a backup, neither of the master table nor of the bookholding one (don't even ask).
I hope you are charging triple rate for this. And once you are done you can sell him a back up solution. 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Also, I know this is going to sound obvious and you already know this information - but it always amazes me that when I have something to do that I know extremely well, but under stress, I always forget something important. So, make sure you have a backup of the files you have now. If they get screwed up more you'll have even less to work with.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Thanks for your post. I know the problem you described and actually have experienced it several times . That why the first thing I did was to copy the data on a network drive somewhere down the road... 
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hello! Anybody can help me to solve this problem?
I have a stored procedure like:
CREATE PROCEDURE PROC
@id int AS
SELECT 1 as tag,
0 as parent,
t1.name as 'PROC!1!name1',
t2.name as 'PROC!1!name2'
FROM Table1 as t1, table2 as t2
WHERE (t1.id = @id AND t1.id = t2.id)
FOR XML EXPLICIT
t1.id and t2.id are keys and there'is a relationship between them.
When I call this procedure an error occurs -
"FOR XML EXPLICIT query contains the invalid column name 'id'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] formatwhere TAGID is a positive integer"
When I change EXPLICIT with AUTO everything is ok
|
|
|
|
|
Hi , i have a small normalization problem.
imagine this:
table User
table City
table Country
each user can be related to a city , and each city can be related to a country
(NOTE: my real problem does not invlove neither users or regions this is just a exampel)
now to the problem.
an User MUST always have a country , but he may or may not have a city assigned
(incase the registration is incomplete... again , note the real world problem invloves products and different categories)
it might be easy to say "why do you want to use something that hasnt been registered 100%"
well.... i have no idea , this is just the way they work here, we sell products even if they havnt been completely registered..
so what would be the correct way to normalize such problem?
should user also relate to country , even though it makes it possible to relate the user to "hungary" and relate the same user to the city "london" , which in turn relates to "england".
OR
should i insert an "empty" element in the city table for each country.
so i can relate the user to an empty element that in turn relates to the correct country?
OR
is there any other "correct" way of doing this???
|
|
|
|
|
Does the database platform you are using support stored procedures?
Darien
C#, VB.NET, Oracle, Sq(uirre)l Server
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
its ms sql . but what has storedprocedures to do with table normalization rules?
//Roger
|
|
|
|
|
Well.... I was brainstorming and trying to think about different ways you could ensure the integrity of the data. i.e. Allowing London, U.K. and disallowing Los Angeles, Nigeria...
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
Speaking strictly from a normalization perspective, since any given user can only have one city or state, I would probably have a CityId and a CountryId element in the users table. I would make the countryId NOTNULLABLE and the cityId NULLABLE. I would then have those two id columns referenced to two lookup tables ( tblCountryLkp, tblCityLkp ? ) that would contain valid entries for their specific entities.
A stored procedure could then be used to reference another table ( tblValidCountryCities ? ) that would contain all valid combinations of cities and countries. Any insertion would be validated against this table and a non-conforming combination would result in an error.
Hope this helps,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
Consider removing city from your user table, and placing it in an associated user_city table (user_city_id, user_id, city_id). This makes it clear that the city is optional information that is not (reliably) part of the core user information.
my blog
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
