How to make MDAC 2.8 behave like 2.7 regarding 'Object was open' e

On SQL Server 2K, recently put on SP4. Some VB code began to break with a
-2147217915 'Object was open' error. The code is opening a new
ADODB.Recordset, and that recordset is already open. This is clearly a bug
in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
present in many code segments so it will take some time to fix it.
My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
memory problem.
Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
be configured to tolerate opening an open recordset?
BBHi
I don't think there is an option to do this. There are several versions of
2.8 you may want to check that is consistent (with the component checker) an
d
if you are on the SP1.
http://msdn.microsoft.com/data/mdac...ds/default.aspx
John
"bearcreek" wrote:

> On SQL Server 2K, recently put on SP4. Some VB code began to break with a
> -2147217915 'Object was open' error. The code is opening a new
> ADODB.Recordset, and that recordset is already open. This is clearly a b
ug
> in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
> present in many code segments so it will take some time to fix it.
> My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
> memory problem.
> Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
> be configured to tolerate opening an open recordset?
> --
> BBsql

How to make MDAC 2.8 behave like 2.7 regarding 'Object was open' e

On SQL Server 2K, recently put on SP4. Some VB code began to break with a
-2147217915 'Object was open' error. The code is opening a new
ADODB.Recordset, and that recordset is already open. This is clearly a bug
in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
present in many code segments so it will take some time to fix it.
My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
memory problem.
Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
be configured to tolerate opening an open recordset?
--
BBHi
I don't think there is an option to do this. There are several versions of
2.8 you may want to check that is consistent (with the component checker) and
if you are on the SP1.
http://msdn.microsoft.com/data/mdac/downloads/default.aspx
John
"bearcreek" wrote:
> On SQL Server 2K, recently put on SP4. Some VB code began to break with a
> -2147217915 'Object was open' error. The code is opening a new
> ADODB.Recordset, and that recordset is already open. This is clearly a bug
> in the VB code, but the bug was tolerated under MDAC 2.7. The bug is
> present in many code segments so it will take some time to fix it.
> My DBA is hounding me to fix the code, he wants to get SP4 on to fix a
> memory problem.
> Is there a way to make MDAC 2.8 work like MDAC 2.7. That is, can MDAC 2.8
> be configured to tolerate opening an open recordset?
> --
> BB

how to maintain database concurrence?

My application uses sql for performing operation. something like conncetion.execute(query). so there is only conncetion object no recordset object or something like that .

i want to run multiple instence of my application so i want to maintain integrety of data. and i am looking for solution through sql for locking mechanism. so concurrent data access dont currept data.

I hope you have uderstand my requirement.Avoid locking whenever possible! Use optimistic concurrency (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcoptimistic_concurrency.asp) instead of relying on locking.

-PatP

how to locate the exact user name who owns a db object

hi all,
In sql 2000, how can you find out the exact user name who owns/created a db
object? They are generally recorded as 'dbo' in the sysobjects table, how
can we find out the specific user login name behind the 'dbo' entry?
many thanks,
JJThe 'dbo' user in a database is a special user that maps to the login
who owns the database (usually the login who initially created the
database). The system stored proc "exec sp_helpdb '<dbname>'" will tell
you who the owner of the database is and that login will be the one that
maps to the dbo user in the database.
Other, less Microsoft approved, ways of finding this info would be:
* "select * from master.dbo.sysdatabases" (the sid column is the
login that owns a given database, ie. that maps to the dbo user in
that database, and you can join that to master.dbo.syslogins to
get more info about that login)
* "select * from <dbname>.dbo.sysusers" (the dbo user in the
database is always uid 1; the sid column in that table will map
back to the master.dbo.syslogins table to tell you who owns the
database...unless the database user is an orphaned user (the sid
doesn't map back to any row in master.dbo.syslogins) which often
happens when you restore DBs from other servers because the other
server has different data in its master.dbo.syslogins table; this
can be corrected with sp_change_users_login)
* You could use the SUSER_SNAME() function with the sysusers table
like this:
select SUSER_SNAME(sid) from <dbname>.dbo.sysusers where uid = 1
Bear in mind, not every object in a DB has to be owned by the dbo user,
although this is quite normal. To find out which DB user owns a
specific object in the database you can use the OBJECTPROPERTY()
function like this:
select USER_NAME(OBJECTPROPERTY(OBJECT_ID('MyTa
ble'),'OwnerId'))
However, sp_helpdb is probably the easiest. Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
JJ Wang wrote:

>hi all,
>In sql 2000, how can you find out the exact user name who owns/created a db
>object? They are generally recorded as 'dbo' in the sysobjects table, how
>can we find out the specific user login name behind the 'dbo' entry?
>many thanks,
>JJ
>|||JJ,
I think that would need to be accomplished by using a source control system
like Visual SourceSafe as many logins may have the ability to have dbo be
the owner of an object.
HTH
Jerry
"JJ Wang" <JJWang@.discussions.microsoft.com> wrote in message
news:73FC551C-3600-4E8F-8B37-B2BB9E890879@.microsoft.com...
> hi all,
> In sql 2000, how can you find out the exact user name who owns/created a
> db
> object? They are generally recorded as 'dbo' in the sysobjects table, how
> can we find out the specific user login name behind the 'dbo' entry?
> many thanks,
> JJ|||Hi,
the only wat i think for that is too tell your developers/DBAs to use full
name while creating db / objects .
Regards

How to load a Legacy DTS Package from .NET?

If I have an object of type "Microsoft.SqlServer.DTS.Runtime.Application,"

can I use one of the following functions
- ExistsOnDTSServer
- ExistsOnSQLServer
- LoadFromDTSServer
- LoadFromSQLServer
- LoadFromSQLServer2

to interact/load with a Legacy (SQL 2000) DTS package, stored on a SQL 2005 machine?

If so, can someone post an example first-argument (the Package Path) ?
Thanks.

I suspect that this is not possible.

Why would you not use the DTS object model to load and run a DTS package? Although it's a COM library, you can certainly use it from .NET...

-Doug|||You're probably right. It was worth someone verifying before assuming correctly/incorrectly.

The "Microsoft DTSPackage Object" COM object is an adequate alternative.
But this would require SQL 2000 Client Tools to be installed on the machine, in addition to SQL 2005 Client Tools, to gain use of both objects.

How to know which tables is updated ?

is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004You could use profiler to audit certain types of activity & subsequently see
what activity has occured through time.
You could use triggers &/or default values to maintain a list of who & when
records are added.
I generally add [Creator] & [CreationDate] fields with defaults of S
USER_SNAME() & GETDATE() respectively to any important tables...
--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004|||You can also purchase Log Explorer from Lumigent (www.lumigent.com)... It ca
n read a log file and tell you the changes that were made...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's communi
ty of SQL Server professionals.
www.sqlpass.org
"Ashish Kanoongo" <ashishk@.armoursoftware.com> wrote in message news:%23xGOR
hBDEHA.3664@.TK2MSFTNGP10.phx.gbl...
is there way to know to which object is updated or in which tables record ha
s entered recently?
is there any utility in Sql Server 2000 or any kind of third party tool avai
lable ?
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.627 / Virus Database: 402 - Release Date: 03/16/2004

How to know if a record exists

Hello,

I have a TextBox and an Insert Button, it works like this:

protected void Button1_Click(object sender, EventArgs e)
 {
 String strConn=SqlDataSource1.ConnectionString;
 SqlCommand cmd =new SqlCommand("INSERT INTO My_Table_1 (Column_1) VALUES ("+TextBox1.Text+")",new SqlConnection(strConn));
 cmd.Connection.Open();
 cmd.ExecuteNonQuery();
 cmd.Connection.Close();
 }

But,what code i must write for checking, if the value i am trying to insert already exists ??

I mean something like this:

if (TextBox1.Text does not Exists on any Record in My_Table_1) then
Insert it
else
Show Message : "Already exists a record with this value"

Thank you SO MUCH, guys,

Carlos.you can execute "select count(*) from My_Table_1 whereColumn_1='"+TextBox1.Text+"'" before insert and get value of the firstcolumn, then use if to determine whether it is bigger than 0|||

you could write a stored procedure and pass the insert parameters to it. In the stored proc you could do a check as follows:

IF NOT EXISTS(SELECT * FROM <table> WHERE <Condition>)

BEGIN

INSERT INTO <table> (<columns>) VALUES (<Values>)

END

If you want to return an appropriate message back to the front end you could include a return value as an OUTPUT parameter and return a code appropriately.

|||Hello Tony and Ndinakar:

thanks for the replies ;)

I will try first Tony's method...

BUT, how can i get the value of the first column ?

Please, can you write some code? i am REALLY new on this, and i can't find the answer :(

Thank you so much,

Carlos.|||code sample for you:)

//open connection
SqlConnection m_conn=new SqlConnection(ConnectionString);
m_conn.Open();
//run sql
sqlstring="select count(*) from My_Table_1 whereColumn_1='"+TextBox1.Text+"'" ";
SqlCommand sqlcmd=new SqlCommand(sqlstring,m_Conn);
SqlDataReader sdr=sqlcmd.ExecuteReader();
sdr.Read();
int rowcount=(int)sdr[0]; //here, you get what you need!
sdr.Close();
//determine whether record exist
if(rowcount==0)
{
//insert a new record
}
//close you connection
m_conn.Close();|||With all due respect... The second solution will give you better performance. It is essentiall the same as the first, except the exists contraint tells the query to stop executing after it finds a single match, whereas the count(*) method continues to aggregate the entire selection.|||Yes, you will be making 2 trips to insert a record which is unnecessary.|||Thank you guys for all your replies.

Tony, your code works 100% for what i am looking for, thank you ;)

In a few days i will study stored procedures, and i will try the second solution, and i will post my results here.

Thanks again,

Carlos.

How to know allocation place for each object?

Hi all of you,

My current dutie is try to obtain for each table their filegroup.

I'm seeing sysobjects table but I can't see nothing related to do with

Thanks a lot for your time,

Ok, if you run this query you obtain such name but it is not enought for my goal:

sp_help <table>

|||

Not sure if this is something you're after, this will list each object with their associated filegroup, you can filter sysobjects for tables only, not pretty unfortunately but works:

SET NOCOUNT ON

DECLARE @.sqltxt varchar(4000);

DECLARE @.tblName varchar(4000);

DECLARE GetFG_Cursor CURSOR FOR

Select 'sp_objectfilegroup ' + CAST(id AS VARCHAR(4000)), name from sysobjects;

OPEN GetFG_Cursor;

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT 'Table Name: ' + @.tblName;

EXECUTE(@.sqltxt);

FETCH NEXT FROM GetFG_Cursor INTO @.sqltxt,@.tblName;

END

CLOSE GetFG_Cursor;

DEALLOCATE GetFG_Cursor;

|||

hi xrayb,

That's a good approximation. Thanks indeed.

It'll be helpful.