Database

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

4-Jul-05 22:50

Since you don't mention which database you are using, I shall assume SQL Server 2000 as this is the most common on this forum.

You can use sp_addlinkedserver[^] to establish the link to the other server, and then you can reference the server in your queries like this:

SELECT * FROM [MyOtherServer].[TheDatabase].[TheOwner].[TheTable]

TheOwner is usually dbo.

Does this help?

I want to change the owner of the object.............

under28114-Jul-05 19:05

under2811

4-Jul-05 19:05

Hello friends!

I want to change the owner of the object

I have table whose owner is dbo but i want to change it to hov
can u please tell me complete syntax of that

Also when we right click on table and select design table , here we find menu like Trigger then Show permission then show dependency like that
If I chose show permission here we find list all users/userdefined database roles/public like that , can u please explain that thing (window) to me what that mean by?

T.I.A
Shashank

Re: I want to change the owner of the object.............

Christian Graus4-Jul-05 19:19

Christian Graus

4-Jul-05 19:19

What are you talking about ? I got to Design Table in SQL Server, then I could not follow your instructions - are you using SQL Server, or something else ?

Not that you need to use SQL Server, but you could provide at least some clue, seeing as your question is not about SQL, ADO, or ADO.NET.

Christian Graus - Microsoft MVP - C++

Re: I want to change the owner of the object.............

under28114-Jul-05 19:41

under2811

4-Jul-05 19:41

under2811Hi
I am asking two thing first is how to change owner of a table
i.e. I have table whose owner is 'dbo' and now I want to change owner dbo to owner 'hov'
How to change that???

second thing i asked if we open E.M here we find lots of table suppose if I right click on one table say employees then we get menus like new table,design table,open table like that if i cliked on design table , we find another more menus like set primary keys,triggers,show permissions like that now i want to know what this term 'show permission' is???
please explain in detail

shashank

Re: I want to change the owner of the object.............

Christian Graus4-Jul-05 19:46

Christian Graus

4-Jul-05 19:46

Ah, I see. It shows the users of the database, and what permissions each of them has in regard to that table.

Or, if you don't know what it is, don't mess with it Smile | :)

The answer to question 1 is probably sp_changeobjectowner. Look it up in the SQL Server books that come with the product.

Christian Graus - Microsoft MVP - C++

Re: I want to change the owner of the object.............

Scott Serl5-Jul-05 12:38

Scott Serl

5-Jul-05 12:38

I don't know how to do it from enterprize manager, but you can run sp_changeobjectowner from query analyzer to change the ownership of a table.

Anonymous4-Jul-05 18:56

Anonymous

4-Jul-05 18:56

How to optimise performance of retrieving multiple sql function results from DB

luckyeagle4-Jul-05 12:50

luckyeagle

4-Jul-05 12:50

I got an application connected to a piece of local MS Access database.
My goal is to get results of 50 count numbers from a table.
So what I did is
1)Create a stored procedure in Access "parameter ... select count(*) from table between '$inputA' and '$inputB'". Test it in Access,and it works well to get correct results.
2)In my C# application I create a oledbconnection with Access file and set the command type to be storedprocedure, then call it using executeScaler with different input parameters for 50 times.

It seems the performance is so slow D'Oh! | :doh:

Is there anyone with better logic to improve this design and make the program faster?

Thanks

Re: How to optimise performance of retrieving multiple sql function results from DB

Rob Graham4-Jul-05 13:14

Re: How to optimise performance of retrieving multiple sql function results from DB

4-Jul-05 13:14

What kind of data is in the table?
What is the primary key for the table?
How many rows in the table?
what is the data type for the input parameters?
how many other columns in the table (the query doesn't look syntactically correct- shouldn't it be "select count(*) from [tablename] where [columnname] between..."
What were the timing results (and on what platform), and what were your expectations.

In other words, a little more info might help someone help you... Perhaps even some example code (closing and re-opening the connection on every iteration can be a performance killer...)

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

luckyeagle4-Jul-05 14:43

luckyeagle

4-Jul-05 14:43

There are two tables related to select statement: Logs and Users
Logs(3 columns) 1500 rows
LogID(auto number; it is the PK) room(number) LogTime dateTime
eg. 1 301 10/12/2004
Reserchers(3 columns) 3 rows
resID(auto number; it is the PK) room(number) project(text)
eg. 8722 301 ‘computer vision’

Stored procedure:
PARAMETERS startDate DateTime, endDate DateTime;
SELECT Count(*)
FROM Logs AS l, Researchers AS r
WHERE (((l.LogTime) Between [startDate] And [endDate])) And l.room=r.room And r.project='computer vision';

My desktop: CPU: PIII733; Memory 256MB
Development tools: VS.Net2003 and MS Access

In the application program I simply use a for loop with start date and end date decreasing.
Array.Add((int)this.odbCommand.ExecuteScalar());

After leave the connection open only once the main form is created then close it in the form's dispose method. I got timing results: 7 seconds to retrieve the 50 results, and I expect it takes less than one second.

Re: How to optimise performance of retrieving multiple sql function results from DB

Rob Graham5-Jul-05 5:56

5-Jul-05 5:56

First, I would note that the autonumber columns are serving no useful purpose other than to satisfy Access's desire that you give a table a PK. They have no relation to the data in either table. Typically one would use an autonumber in one table as the foreign key in related tables to related data in different tables (in your case the related data seems to be the room number, so it might have made sense to used the resID from Researchers as a foreign key in the logs table instead of room number, this would at least have insured that all the records in Logs matched a record in Researchers - assuming that this is expected).

A join might make this a bit faster:

"select Count(*) from Logs as L inner join Researchers as R on L.room = R.room
Where r.project = 'computer vision' and L.Logtime >= [startDate] and L.LogTime <= [endDate]"

(The >=/<= operators are equivalent to the Between operator, but could be faster depending on the SQL parser)
You should also create an index on the room and logdate fields in the Logs table ( in the field properties for each, change indexed to "Yes, duplicates allowed" )

using the System.data.oledb namespace equivalents may be faster than the system.data.odbc ones, the oledb stuff is newer, and likely more optimised for .Net and the Jet engine.

256 MB is a pretty small footprint these days, you may want to insure that you aren't page swapping. Also, access is a file based database, and not a very performant one at that, so your expectations could be a bit high. You're seeing about 140 ms per query and expecting > 20. Make sure the database is compacted before running your tests, if the database is internally fragmented (from a lot of development changes or data deletion) it could markedly effect the results.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

T-QL Problem

WDI4-Jul-05 10:38

WDI

4-Jul-05 10:38

i use some codeto search on access db
suppose on table T there is two fields ArtistID(Integer) , status(Text)

i got error on :
.
.
.
public string GetCOMMTXT()
{
string sql="select * from T whre";
int ArtistID = 5;
string Status = "A";

sql += "artistID = " + ArtistID + " " ;----> there is no Error

sql += "status = " + "'"+Status+"'";----> there is Error

or if i wrote

sql += "status = " +Status;----> there is Error

.
.
.
return sql;
}
what should i do?

Colin Angus Mackay4-Jul-05 11:32

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

4-Jul-05 11:32

WDI wrote:
what should i do?

I'd fix the error.

First, the code you've presented here is not the code you've got a problem with because there are more errors in it than you present. For instance, the word "where" is misspelled.

Second, and I'm sure I've told you this before, you should use parameterised queries. Then you don't need to worry about stringing together fragments of SQL like this. And, it also improves the security of your application, reducing the risk that someone will be able to successfully launch an attack on it. For more information you can read SQL Injection Attacks and Tips on How to Prevent Them[^]. This acticle actually shows you how to use parameterised queries. I don't know why you don't use them. It makes everything so much easier.

Just for your information - I fully expect you to change this to use parameterised queries - The reason for the error is that you need to put an AND or OR between the conditions in a WHERE clause.

Another tip, put a breakpoint on the return statement and examine the contents of the sql string - it is much easier to read that than trying to decipher what the string should look like based on code that concatenates lots of little strings together.

Does this help?

WDI4-Jul-05 21:05

WDI

4-Jul-05 21:05

I do waht you told but get

Syntax error (missing operator) in query expression 'AND( styleID = @styleID )'.

my method is :

public static IDataReader GetFilteredProduct( FilterEN filterEN )
{
OleDbConnection oleDbConnection;

OleDbCommand oleDbCommand = new OleDbCommand();

OleDbDataReader oleDbDataReader = null;

oleDbCommand.CommandText = "select Product.productID , Product.productName ,
Product.productInfo , Product.productPrice ,
Product.dateOfInsert , Product.status ,
Product.artistID , Artist.artistName from Product
inner join Artist on Product.artistID =
Artist.artistID ";

oleDbConnection = new OleDbConnection (ConfigurationSettings.AppSettings["DbC"]);
try
{
oleDbConnection.Open();

string boolian = ( filterEN.Boolian == 1 ? "OR" : "AND" );

if ( filterEN.StyleID > 0 || filterEN.ArtistID > 0 )

{
oleDbCommand.CommandText += " where ";
}
if ( filterEN.StyleID > 0 )
{
oleDbCommand.CommandText += boolian + "( styleID = @styleID )";

OleDbParameter paramStyleID = new OleDbParameter ( "@styleID" ,
OleDbType.Integer );
paramStyleID.Value = filterEN.StyleID;

oleDbCommand.Parameters.Add ( paramStyleID );

}
if ( filterEN.ArtistID > 0 )
{
oleDbCommand.CommandText += boolian + "artistID = @artistID ";

OleDbParameter paramArtistID = new OleDbParameter ( "@artistID" ,
OleDbType.Integer );
paramArtistID.Value = filterEN.ArtistID;

oleDbCommand.Parameters.Add ( paramArtistID );

}

oleDbCommand.Connection = oleDbConnection;

oleDbDataReader = oleDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch( Exception ex)
{
throw ex;
}

return oleDbDataReader;
}

Colin Angus Mackay4-Jul-05 22:46

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

4-Jul-05 22:46

You should put a breakpoint at the start of this code and watch the CommandText being built up. You will then see where your logic error is.

You are adding an AND immediately after the WHERE and this is causing a syntax error. WHERE AND( styleID = @styleID ) is not valid. You need to check in your code whether a filter expression has already been placed in the WHERE clause. Obviously, for StyleID you don't need to check as this is the first filter expression, so nothing comes before it. You can remove the boolian+ (by the way that should be spelt "boolean") in the line that adds the StyleID to the SQL Statement.

Does this help?

WDI5-Jul-05 3:29

WDI

5-Jul-05 3:29

thanks i got it , with this change it fixed!

oleDbCommand.CommandText += " where 1=1";

Colin Angus Mackay5-Jul-05 3:38

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

5-Jul-05 3:38

WDI wrote:
oleDbCommand.CommandText += " where 1=1";

I guess if it works..... However, that is certainly one solution I wouldn't have thought of.

LDAP Frontend to MSSQL

MrEyes4-Jul-05 2:13

MrEyes

4-Jul-05 2:13

The title says it all really.

I am looking for something that will allow me to put an LDAP frontend onto and MSSQL server. i.e. Application creates and LDAP query that is received by the LDAP frontend, but the return data is in an MSSQL database.

Does anybody have any information/links on how this can be done?

post.mode = postmodes.signature;
SELECT everything FROM everywhere WHERE something = something_else;
> 1 Row Returned
> 42

IMPORT ACCESS FROM SQL SERVER 2000

RajithCAlwis4-Jul-05 0:47

RajithCAlwis

4-Jul-05 0:47

i import a huge database from access to sql server 2000, in Access database there were a field which is auto increment and primery key,but when i check SQL SERVER auto increment constraint not there and im not able to change data type to unique identifier field ,how i change it to auto increment?

Rajith Alwis

Re: IMPORT ACCESS FROM SQL SERVER 2000

Rob Graham4-Jul-05 13:23

Re: IMPORT ACCESS FROM SQL SERVER 2000

4-Jul-05 13:23

if it is an integer field, set the "identity" property to true and the "increment" property to 1.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

RajithCAlwis4-Jul-05 17:02

RajithCAlwis

4-Jul-05 17:02

thanx Rob,
earlier i didnt see the identity increment field its appear only when we set the identity property true, thanx
Rajith Alwis

Paradox lock

Screwtape4-Jul-05 0:29

Screwtape

4-Jul-05 0:29

Hello..
I am connected with a database paradox through Jet Driver.
L " Provider=Microsoft. Jet. OLEDB.4.0; Data Source = \\ server\db; Extended Properties=Paradox 5. X; ".
The problem, other program working through DBE does not give me to open the tables.
How to me to force to work two programs in common?

Re: Paradox lock

Rob Graham4-Jul-05 13:25