DSO in SQL2005: message

Showing posts with label message. Show all posts

Sunday, March 25, 2012

Droppin rule in SQL Server 2000

I am trying to drop a rule but I am getting the following error message:
"The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
more column".
I tried to run sp_unbindrule but it gave me this error message: The data
type "RULE_NAME" does not exist.
How can I resolve this? Thank you in advanceA rule can be bound to either a particular column or to a user defined
datatype. What is the name of the rule? Also what is the name the table and
the column?
Can you post the syntax you were using?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>I am trying to drop a rule but I am getting the following error message:
> "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> more column".
> I tried to run sp_unbindrule but it gave me this error message: The data
> type "RULE_NAME" does not exist.
> How can I resolve this? Thank you in advance|||Thanks for your response Kalen.
I could only get some info using sp_help stored procedure. It says owner is
dbo and type = rule.
It is using the table tblEmpType. I am using the syntax as
DROP RULE [dbo].[Rule_Sim_App]
I forgot to mention this in my previous post. When I check in EM -->
Database --> Rules tab, I do not see any rule created by this name
Rule_Sim_App.
Please advise.
"Kalen Delaney" wrote:
> A rule can be bound to either a particular column or to a user defined
> datatype. What is the name of the rule? Also what is the name the table and
> the column?
> Can you post the syntax you were using?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >I am trying to drop a rule but I am getting the following error message:
> >
> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one or
> > more column".
> >
> > I tried to run sp_unbindrule but it gave me this error message: The data
> > type "RULE_NAME" does not exist.
> >
> > How can I resolve this? Thank you in advance
>
>|||What syntax are you using to try to unbind the rule
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> Thanks for your response Kalen.
> I could only get some info using sp_help stored procedure. It says owner
> is
> dbo and type = rule.
> It is using the table tblEmpType. I am using the syntax as
> DROP RULE [dbo].[Rule_Sim_App]
> I forgot to mention this in my previous post. When I check in EM -->
> Database --> Rules tab, I do not see any rule created by this name
> Rule_Sim_App.
> Please advise.
> "Kalen Delaney" wrote:
>> A rule can be bound to either a particular column or to a user defined
>> datatype. What is the name of the rule? Also what is the name the table
>> and
>> the column?
>> Can you post the syntax you were using?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >I am trying to drop a rule but I am getting the following error message:
>> >
>> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
>> > or
>> > more column".
>> >
>> > I tried to run sp_unbindrule but it gave me this error message: The
>> > data
>> > type "RULE_NAME" does not exist.
>> >
>> > How can I resolve this? Thank you in advance
>>|||sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
data type "RULE_NAME" does not exist.
Thanks again
----
"Kalen Delaney" wrote:
> What syntax are you using to try to unbind the rule
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> > Thanks for your response Kalen.
> >
> > I could only get some info using sp_help stored procedure. It says owner
> > is
> > dbo and type = rule.
> >
> > It is using the table tblEmpType. I am using the syntax as
> > DROP RULE [dbo].[Rule_Sim_App]
> >
> > I forgot to mention this in my previous post. When I check in EM -->
> > Database --> Rules tab, I do not see any rule created by this name
> > Rule_Sim_App.
> >
> > Please advise.
> >
> > "Kalen Delaney" wrote:
> >
> >> A rule can be bound to either a particular column or to a user defined
> >> datatype. What is the name of the rule? Also what is the name the table
> >> and
> >> the column?
> >> Can you post the syntax you were using?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >I am trying to drop a rule but I am getting the following error message:
> >> >
> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to one
> >> > or
> >> > more column".
> >> >
> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> > data
> >> > type "RULE_NAME" does not exist.
> >> >
> >> > How can I resolve this? Thank you in advance
> >>
> >>
> >>
>
>|||Hi David
Please check the syntax in BOL for sp_unbindrule. The parameter is what you
are unbinding from, either a user defined type or a 'tablename.columnname'.
Since there can only be one rule bound to anything, there is no ambiguity.
The message is indicating that it thinks Rule_Sim_App is a type name, since
it is not in the format of a table and column.
You need to find the name of the table and the column the rule is bound to,
and then use them in the sp_unbindrule statement.
This query might give you a start:
select name as col_name, object_name(id) as table_name from syscolumns
where domain = object_id ('Rule_Sim_App')
Once you have the table and column, you can run sp_unbindrule:
exec sp_unbindrule 'tablename.columnname'
Again, you do not use the name of the rule when calling sp_unbindrule.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> data type "RULE_NAME" does not exist.
> Thanks again
> ----
> "Kalen Delaney" wrote:
>> What syntax are you using to try to unbind the rule
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> > Thanks for your response Kalen.
>> >
>> > I could only get some info using sp_help stored procedure. It says
>> > owner
>> > is
>> > dbo and type = rule.
>> >
>> > It is using the table tblEmpType. I am using the syntax as
>> > DROP RULE [dbo].[Rule_Sim_App]
>> >
>> > I forgot to mention this in my previous post. When I check in EM -->
>> > Database --> Rules tab, I do not see any rule created by this name
>> > Rule_Sim_App.
>> >
>> > Please advise.
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> A rule can be bound to either a particular column or to a user defined
>> >> datatype. What is the name of the rule? Also what is the name the
>> >> table
>> >> and
>> >> the column?
>> >> Can you post the syntax you were using?
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >I am trying to drop a rule but I am getting the following error
>> >> >message:
>> >> >
>> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
>> >> > one
>> >> > or
>> >> > more column".
>> >> >
>> >> > I tried to run sp_unbindrule but it gave me this error message: The
>> >> > data
>> >> > type "RULE_NAME" does not exist.
>> >> >
>> >> > How can I resolve this? Thank you in advance
>> >>
>> >>
>> >>
>>|||Bull's eye Kalen. I really appreciate it.
I have asked the developer to use CHECK constraint instead of using bind
rule objects. Is check constraint easy to administer than bind rule objects?
Thanks again ...
"Kalen Delaney" wrote:
> Hi David
> Please check the syntax in BOL for sp_unbindrule. The parameter is what you
> are unbinding from, either a user defined type or a 'tablename.columnname'.
> Since there can only be one rule bound to anything, there is no ambiguity.
> The message is indicating that it thinks Rule_Sim_App is a type name, since
> it is not in the format of a table and column.
> You need to find the name of the table and the column the rule is bound to,
> and then use them in the sp_unbindrule statement.
> This query might give you a start:
> select name as col_name, object_name(id) as table_name from syscolumns
> where domain = object_id ('Rule_Sim_App')
> Once you have the table and column, you can run sp_unbindrule:
> exec sp_unbindrule 'tablename.columnname'
> Again, you do not use the name of the rule when calling sp_unbindrule.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
> > data type "RULE_NAME" does not exist.
> >
> > Thanks again
> > ----
> >
> > "Kalen Delaney" wrote:
> >
> >> What syntax are you using to try to unbind the rule
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
> >> > Thanks for your response Kalen.
> >> >
> >> > I could only get some info using sp_help stored procedure. It says
> >> > owner
> >> > is
> >> > dbo and type = rule.
> >> >
> >> > It is using the table tblEmpType. I am using the syntax as
> >> > DROP RULE [dbo].[Rule_Sim_App]
> >> >
> >> > I forgot to mention this in my previous post. When I check in EM -->
> >> > Database --> Rules tab, I do not see any rule created by this name
> >> > Rule_Sim_App.
> >> >
> >> > Please advise.
> >> >
> >> > "Kalen Delaney" wrote:
> >> >
> >> >> A rule can be bound to either a particular column or to a user defined
> >> >> datatype. What is the name of the rule? Also what is the name the
> >> >> table
> >> >> and
> >> >> the column?
> >> >> Can you post the syntax you were using?
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >> www.solidqualitylearning.com
> >> >>
> >> >>
> >> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
> >> >> >I am trying to drop a rule but I am getting the following error
> >> >> >message:
> >> >> >
> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound to
> >> >> > one
> >> >> > or
> >> >> > more column".
> >> >> >
> >> >> > I tried to run sp_unbindrule but it gave me this error message: The
> >> >> > data
> >> >> > type "RULE_NAME" does not exist.
> >> >> >
> >> >> > How can I resolve this? Thank you in advance
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Good idea. Bound rules will be going away in a future version.
Check constraints may be easier to keep track of since they are attached to
a single table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:65F55A05-C4DC-4E08-803A-FE527C4506A4@.microsoft.com...
> Bull's eye Kalen. I really appreciate it.
> I have asked the developer to use CHECK constraint instead of using bind
> rule objects. Is check constraint easy to administer than bind rule
> objects?
> Thanks again ...
>
> "Kalen Delaney" wrote:
>> Hi David
>> Please check the syntax in BOL for sp_unbindrule. The parameter is what
>> you
>> are unbinding from, either a user defined type or a
>> 'tablename.columnname'.
>> Since there can only be one rule bound to anything, there is no
>> ambiguity.
>> The message is indicating that it thinks Rule_Sim_App is a type name,
>> since
>> it is not in the format of a table and column.
>> You need to find the name of the table and the column the rule is bound
>> to,
>> and then use them in the sp_unbindrule statement.
>> This query might give you a start:
>> select name as col_name, object_name(id) as table_name from syscolumns
>> where domain = object_id ('Rule_Sim_App')
>> Once you have the table and column, you can run sp_unbindrule:
>> exec sp_unbindrule 'tablename.columnname'
>> Again, you do not use the name of the rule when calling sp_unbindrule.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:3A5A21B4-D7BD-48C2-A695-61A70BD3D04E@.microsoft.com...
>> > sp_unbindrule 'Rule_Sim_App' and it gives me this error message: The
>> > data type "RULE_NAME" does not exist.
>> >
>> > Thanks again
>> > ----
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> What syntax are you using to try to unbind the rule
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.solidqualitylearning.com
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:7FF32AE3-ABFF-4BFF-8B7E-7CA8BC2ADB0E@.microsoft.com...
>> >> > Thanks for your response Kalen.
>> >> >
>> >> > I could only get some info using sp_help stored procedure. It says
>> >> > owner
>> >> > is
>> >> > dbo and type = rule.
>> >> >
>> >> > It is using the table tblEmpType. I am using the syntax as
>> >> > DROP RULE [dbo].[Rule_Sim_App]
>> >> >
>> >> > I forgot to mention this in my previous post. When I check in EM -->
>> >> > Database --> Rules tab, I do not see any rule created by this name
>> >> > Rule_Sim_App.
>> >> >
>> >> > Please advise.
>> >> >
>> >> > "Kalen Delaney" wrote:
>> >> >
>> >> >> A rule can be bound to either a particular column or to a user
>> >> >> defined
>> >> >> datatype. What is the name of the rule? Also what is the name the
>> >> >> table
>> >> >> and
>> >> >> the column?
>> >> >> Can you post the syntax you were using?
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >> www.solidqualitylearning.com
>> >> >>
>> >> >>
>> >> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> >> news:451299E3-867E-44C6-B110-55DE3F8D4517@.microsoft.com...
>> >> >> >I am trying to drop a rule but I am getting the following error
>> >> >> >message:
>> >> >> >
>> >> >> > "The rule 'dbo.RULE_NAME' cannot be dropped because it is bound
>> >> >> > to
>> >> >> > one
>> >> >> > or
>> >> >> > more column".
>> >> >> >
>> >> >> > I tried to run sp_unbindrule but it gave me this error message:
>> >> >> > The
>> >> >> > data
>> >> >> > type "RULE_NAME" does not exist.
>> >> >> >
>> >> >> > How can I resolve this? Thank you in advance
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Thursday, March 22, 2012

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:
> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.sql

Wednesday, March 21, 2012

DROP TABLE failed

An overnight run failed, and I now regularly get the message:
Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
It appears that a DROP TABLE failed - the table does not show up in
Enterprise Manager, but a row for the table appears in sysobjects. DBCC
CHECKTABLE did not help.
How can I safely get out of this? Presumably it is not safe to delete the
row from sysobjects?
In future, would it help to put the DROP TABLE instruction within a
transaction?
Thanks.
--
Peter HyssettYeah, that can happen, here is another post which clearly identifies the
problem and give a hint how to solve it:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/e97a7097d5e766ff/db5b5409616cfed4?q=%22Drop+Table%22+%22exists+in+sysobjects%22&rnum=3&hl=de#db5b5409616cfed4
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> schrieb im
Newsbeitrag news:732EDDE9-15C5-42D4-9A81-F4A80E951320@.microsoft.com...
> An overnight run failed, and I now regularly get the message:
> Could not find row in sysindexes for database ID nn, object ID nnnnnnnnnn,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> It appears that a DROP TABLE failed - the table does not show up in
> Enterprise Manager, but a row for the table appears in sysobjects. DBCC
> CHECKTABLE did not help.
> How can I safely get out of this? Presumably it is not safe to delete the
> row from sysobjects?
> In future, would it help to put the DROP TABLE instruction within a
> transaction?
> Thanks.
> --
> Peter Hyssett

Drop table causes system to hang

I have a table in SQL 2000 that I cannot drop with either Enterprise Manager
or Query Analyzer (drop table tblName). I don't get an error message, I just
get the hour glass. I got the data out of it - I could ignore it, but I'd
rather get rid of it. I created it with a create table statement, if that
makes any difference.
It must be being used by some process somehow.
You may set its access to Restrict Access: Members of... from the
database's properties and then try dropping it again?
Or detach that database and delete its "mdf" (ndf) and "ldf" files.
Ekrem ?nsoy
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>I have a table in SQL 2000 that I cannot drop with either Enterprise
>Manager
> or Query Analyzer (drop table tblName). I don't get an error message, I
> just
> get the hour glass. I got the data out of it - I could ignore it, but I'd
> rather get rid of it. I created it with a create table statement, if that
> makes any difference.
|||Hi Ekrem,
How detaching and deleting mdf and ldf files can help to drop a table?
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ekrem ?nsoy" wrote:

> It must be being used by some process somehow.
> You may set its access to Restrict Access: Members of... from the
> database's properties and then try dropping it again?
> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> --
> Ekrem ?nsoy
>
> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>
|||I guess I was kinda drunk while typing that =) I probably assumed he wanted
to delete the database itself.
However it could work in terms of disconnecting processes which use the
mentioned table.
Ekrem ?nsoy
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> How detaching and deleting mdf and ldf files can help to drop a table?
> Thanks,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Ekrem ?nsoy" wrote:
|||How about some constructive advice?
"Ekrem ?nsoy" wrote:

> I guess I was kinda drunk while typing that =) I probably assumed he wanted
> to delete the database itself.
> However it could work in terms of disconnecting processes which use the
> mentioned table.
> --
> Ekrem ?nsoy
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
>

Monday, March 19, 2012

drop manually table (on the subscriber) that was part of merge p

Hi ,
I am trying to drop manually table (on the subscriber) that was part of
merge publication before but got the below message. The publication was
dropped using E.M and even when try to re-create merge publication and add
this table it failed due to the fact that the it can not drop the specific
table on the subscriber .
Can I find any system store proc that force drop this table? Can I drop
it manually from system table (what tables )?
The message is :
Cannot drop the table 'XXXX' because it is being used for replication.
There is a stored procedure to do this called sp_MSunmarkreplinfo which
takes a tablename as a parameter. Alternatively, setting replinfo to 0 in
sysobjects for the particular table should do it. Finally, running
sp_removedbreplication can be used to remove all traces of replication in
the subscriber database, but obviously must only be done if this database is
not also configured as a publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Wednesday, March 7, 2012

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a database. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly appreciated.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LDF'"
Thanks,
Ram
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/************************************************** *************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
************************************************** **************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>
|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
>news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a datab
ase. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is
there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly apprecia
ted.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS
', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LD
F'"
Thanks,
RamRam
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/ ****************************************
***********************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
****************************************
************************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
> news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Sunday, February 26, 2012

Driver''s SQLSetConnectAttr failed from script component

I am currently trying to get a script component to write data into a openedge database.

I am stumped by the below message.

Validation error. Bulk Load ProjPeriod Bplan: Script Component [9378]: System.Data.Odbc.OdbcException:

ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.

ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server rejects connection on attach.

ERROR [IM006] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Driver's SQLSetConnectAttr failed. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Package.dtsx 0 0

The two areas’ that I have found that might be causing this is transaction and codepage, however I have tried setting these appropriately with no success.

Any help would be much appreciated.

Cheers,

Ken C

Does the connection work correctly if you use the connection manager from a DataReader source?

|||

Hi jwelch

As far as I can tell, using this connection from connection manager with Datareader does work.

I created the connection and used in a datareader and it displayed the field names from the supplied sql.

Cheers,

Ken

|||

Hi Again

A bit wierd.... I changed the Fetch Array Size, in the odbc connection to 0 .....this seemed to remove the error from ssis. (This seemed to work for somone else connecting to Oracle db)

Since then i have put the value back to 50 and the error messege has not returned ?

Will develop futher and post back

Cheers

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

I am seeing a few of the below listed error message on our application
servers that are connecting to our SQL 2005 servers. What do they mean and
how can I resolve them ?
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC
failed
Hi
You can call SQLGetDiagRec with a HandleType of SQL_HANDLE_DBC and a Handle
of you ConnectionHandle to get more information about the error. See
http://msdn2.microsoft.com/en-us/library/ms711810.aspx
John
"Hassan" wrote:

> I am seeing a few of the below listed error message on our application
> servers that are connecting to our SQL 2005 servers. What do they mean and
> how can I resolve them ?
> [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC
> failed
>
>
>

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

I am seeing a few of the below listed error message on our application
servers that are connecting to our SQL 2005 servers. What do they mean and
how can I resolve them ?
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HAND
LE_DBC
failedHi
You can call SQLGetDiagRec with a HandleType of SQL_HANDLE_DBC and a Handle
of you ConnectionHandle to get more information about the error. See
http://msdn2.microsoft.com/en-us/library/ms711810.aspx
John
"Hassan" wrote:

> I am seeing a few of the below listed error message on our application
> servers that are connecting to our SQL 2005 servers. What do they mean and
> how can I resolve them ?
> [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HA
NDLE_DBC
> failed
>
>
>

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

I am seeing a few of the below listed error message on our application
servers that are connecting to our SQL 2005 servers. What do they mean and
how can I resolve them ?
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC
failedHi
You can call SQLGetDiagRec with a HandleType of SQL_HANDLE_DBC and a Handle
of you ConnectionHandle to get more information about the error. See
http://msdn2.microsoft.com/en-us/library/ms711810.aspx
John
"Hassan" wrote:
> I am seeing a few of the below listed error message on our application
> servers that are connecting to our SQL 2005 servers. What do they mean and
> how can I resolve them ?
> [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC
> failed
>
>
>

Driver caching db?

Hello,
I'm not sure if this message belongs in microsoft.public.sqlserver.msde
or is more appropriate here. Please accept my apologies if I have the
wrong list.
I've written ODBC source in C++ that accesses an MSDE instance on a
local machine. I perform the usual steps:
1. Allocate an SQLHENV
2. Allocate the SQLHDBC
3. Connect
4. Execute a statement to "use " my database
5. Issue a simple query
6. Process results
7. Disconnect SQLHDBC
8. Free SQLHDBC
9. Free SQLHENV
10. Exit application
Then I try to attach to the database in SQL Server Enterprise Manager
which returns the following error:
"Error 5123: CREATE FILE encountered operating system error 32 (The
process cannot access the file because it is being used by another
process.) while attempting to open or create the physical file:
'C:\mydb.mdf'.
Do I need to do something different to force the driver to unload the
database when the application ends?
Thanks,
John
The driver does not touch the database file directly. The SQL Server
process will open (and lock) the database file.
Is the database hosted in a different SQL instance, than the one you are
connecting to in Enterprise Manager?
Brannon
"JohnD" <john_nospam@.tech-testing.com> wrote in message
news:hqpUc.8189$YU1.7939@.newssvr22.news.prodigy.co m...
> Hello,
> I'm not sure if this message belongs in microsoft.public.sqlserver.msde
> or is more appropriate here. Please accept my apologies if I have the
> wrong list.
> I've written ODBC source in C++ that accesses an MSDE instance on a
> local machine. I perform the usual steps:
> 1. Allocate an SQLHENV
> 2. Allocate the SQLHDBC
> 3. Connect
> 4. Execute a statement to "use " my database
> 5. Issue a simple query
> 6. Process results
> 7. Disconnect SQLHDBC
> 8. Free SQLHDBC
> 9. Free SQLHENV
> 10. Exit application
> Then I try to attach to the database in SQL Server Enterprise Manager
> which returns the following error:
> "Error 5123: CREATE FILE encountered operating system error 32 (The
> process cannot access the file because it is being used by another
> process.) while attempting to open or create the physical file:
> 'C:\mydb.mdf'.
> Do I need to do something different to force the driver to unload the
> database when the application ends?
> Thanks,
> John
|||Brannon Jones wrote:
> Is the database hosted in a different SQL instance, than the one you are
> connecting to in Enterprise Manager?
Yes it is. I'm assuming (really I didn't know this...) that this makes
a different?
The application is using a default SQLSERVER instance and the attaching
is occurring in another DBINSTANCE.
|||I don't think you can attach a single database to two different SQL
instances. The first instance that loads the database locks it for
exclusive access. So the second instance that tries to load the database
wont be able to.
Brannon
"JohnD" <john_nospam@.tech-testing.com> wrote in message
news:d6vUc.1687$lY3.1035@.newssvr23.news.prodigy.co m...
> Brannon Jones wrote:
> Yes it is. I'm assuming (really I didn't know this...) that this makes
> a different?
> The application is using a default SQLSERVER instance and the attaching
> is occurring in another DBINSTANCE.
|||Brannon Jones wrote:

> I don't think you can attach a single database to two different SQL
> instances. The first instance that loads the database locks it for
> exclusive access. So the second instance that tries to load the database
> wont be able to.
Which explains why I have to stop the instance in order for the other
instance to attach to it.
Thank you very much Brannon for the clarification.

Driver caching db?

Hello,
I'm not sure if this message belongs in microsoft.public.sqlserver.msde
or is more appropriate here. Please accept my apologies if I have the
wrong list.
I've written ODBC source in C++ that accesses an MSDE instance on a
local machine. I perform the usual steps:
1. Allocate an SQLHENV
2. Allocate the SQLHDBC
3. Connect
4. Execute a statement to "use " my database
5. Issue a simple query
6. Process results
7. Disconnect SQLHDBC
8. Free SQLHDBC
9. Free SQLHENV
10. Exit application
Then I try to attach to the database in SQL Server Enterprise Manager
which returns the following error:
"Error 5123: CREATE FILE encountered operating system error 32 (The
process cannot access the file because it is being used by another
process.) while attempting to open or create the physical file:
'C:\mydb.mdf'.
Do I need to do something different to force the driver to unload the
database when the application ends?
Thanks,
JohnThe driver does not touch the database file directly. The SQL Server
process will open (and lock) the database file.
Is the database hosted in a different SQL instance, than the one you are
connecting to in Enterprise Manager?
Brannon
"JohnD" <john_nospam@.tech-testing.com> wrote in message
news:hqpUc.8189$YU1.7939@.newssvr22.news.prodigy.com...
> Hello,
> I'm not sure if this message belongs in microsoft.public.sqlserver.msde
> or is more appropriate here. Please accept my apologies if I have the
> wrong list.
> I've written ODBC source in C++ that accesses an MSDE instance on a
> local machine. I perform the usual steps:
> 1. Allocate an SQLHENV
> 2. Allocate the SQLHDBC
> 3. Connect
> 4. Execute a statement to "use " my database
> 5. Issue a simple query
> 6. Process results
> 7. Disconnect SQLHDBC
> 8. Free SQLHDBC
> 9. Free SQLHENV
> 10. Exit application
> Then I try to attach to the database in SQL Server Enterprise Manager
> which returns the following error:
> "Error 5123: CREATE FILE encountered operating system error 32 (The
> process cannot access the file because it is being used by another
> process.) while attempting to open or create the physical file:
> 'C:\mydb.mdf'.
> Do I need to do something different to force the driver to unload the
> database when the application ends?
> Thanks,
> John|||Brannon Jones wrote:
> Is the database hosted in a different SQL instance, than the one you are
> connecting to in Enterprise Manager?
Yes it is. I'm assuming (really I didn't know this...) that this makes
a different?
The application is using a default SQLSERVER instance and the attaching
is occurring in another DBINSTANCE.|||I don't think you can attach a single database to two different SQL
instances. The first instance that loads the database locks it for
exclusive access. So the second instance that tries to load the database
wont be able to.
Brannon
"JohnD" <john_nospam@.tech-testing.com> wrote in message
news:d6vUc.1687$lY3.1035@.newssvr23.news.prodigy.com...
> Brannon Jones wrote:
> Yes it is. I'm assuming (really I didn't know this...) that this makes
> a different?
> The application is using a default SQLSERVER instance and the attaching
> is occurring in another DBINSTANCE.|||Brannon Jones wrote:

> I don't think you can attach a single database to two different SQL
> instances. The first instance that loads the database locks it for
> exclusive access. So the second instance that tries to load the database
> wont be able to.
Which explains why I have to stop the instance in order for the other
instance to attach to it.
Thank you very much Brannon for the clarification.

Friday, February 24, 2012

Driver

Hi All,
I’m developing a simple program which will connect to a SQL Server
2000, but when I install the SQL Server I got a message inform me that I
cannot complete, once I check the ODBC drivers I cannot find any of them.
I’m using a Win XP home edition, although I’ve tried to install the
MDAC it inform me that it cannot be run in such version.
Any help, Thanks in advance.
Kind Regards,
MohammadWhat version of MDAC did you try to install? Make sure it's
one of the later versions. You probably want to check your
MDAC installation with component checker as well. You can
download the tool and MDAC versions from:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
-Sue
On Tue, 4 Oct 2005 04:25:02 -0700, "Mohammed"
<Mohammed@.discussions.microsoft.com> wrote:

>Hi All,
> Im developing a simple program which will connect to a SQL Server
>2000, but when I install the SQL Server I got a message inform me that I
>cannot complete, once I check the ODBC drivers I cannot find any of them.
> Im using a Win XP home edition, although Ive tried to install the
>MDAC it inform me that it cannot be run in such version.
> Any help, Thanks in advance.
>Kind Regards,
>Mohammad
>

Driver

Hi All,
I’m developing a simple program which will connect to a SQL Server
2000, but when I install the SQL Server I got a message inform me that I
cannot complete, once I check the ODBC drivers I cannot find any of them.
I’m using a Win XP home edition, although I’ve tried to install the
MDAC it inform me that it cannot be run in such version.
Any help, Thanks in advance.
Kind Regards,
Mohammad
What version of MDAC did you try to install? Make sure it's
one of the later versions. You probably want to check your
MDAC installation with component checker as well. You can
download the tool and MDAC versions from:
http://msdn.microsoft.com/data/mdac/...s/default.aspx
-Sue
On Tue, 4 Oct 2005 04:25:02 -0700, "Mohammed"
<Mohammed@.discussions.microsoft.com> wrote:

>Hi All,
> Im developing a simple program which will connect to a SQL Server
>2000, but when I install the SQL Server I got a message inform me that I
>cannot complete, once I check the ODBC drivers I cannot find any of them.
> Im using a Win XP home edition, although Ive tried to install the
>MDAC it inform me that it cannot be run in such version.
> Any help, Thanks in advance.
>Kind Regards,
>Mohammad
>

Tuesday, February 14, 2012

Drill down using url access in ASP.net not working

I've been through a good bit of the message boards trying to find an
answer to this problem.
I have an application that uses an iframe to embed a MSRS report. I'm
using URL access to call the report.
The problem is that any drill downs in the report DO NOT actually
expand anything in the report. I've looked at the rc:ReplacementRoot
in some threads but I don't see any differences with that extra
parameter.
Has anyone gotten a drilldown to work on an embedded report?
FYI, I've tryied using the sample ReportViewer sample application as
well and I have the same problem:drill downs don't work. (Makes sense
since the ReportViewer sample just creates an iframe.)
Tested with both MSRS with and without service pack 1.
Thanks,
Eugene KolovyanskyPlease take a look at Report Manager, which is part of the package. Report
Manager is a separate ASP.NET application, just like yours. It uses SOAP
calls to the server for management but it uses URL access and IFrame to
render reports. See if drill down works in ReportManager. If it does, you
should be able to do it the same way. If not, we can investigate this
problem.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Euge" <yevger@.yahoo.com> wrote in message
news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> I've been through a good bit of the message boards trying to find an
> answer to this problem.
> I have an application that uses an iframe to embed a MSRS report. I'm
> using URL access to call the report.
> The problem is that any drill downs in the report DO NOT actually
> expand anything in the report. I've looked at the rc:ReplacementRoot
> in some threads but I don't see any differences with that extra
> parameter.
> Has anyone gotten a drilldown to work on an embedded report?
> FYI, I've tryied using the sample ReportViewer sample application as
> well and I have the same problem:drill downs don't work. (Makes sense
> since the ReportViewer sample just creates an iframe.)
> Tested with both MSRS with and without service pack 1.
> Thanks,
> Eugene Kolovyansky|||Thanks for the quick response.
I've looked a little at how the Report Manager handles reports. Drill
downs work just fine there. I've noticed a strange issue with drill
downs and images while pursuing this.
Let's say I have a page with two frames.Menu frame points all links to
the main frame. If I have a single link that opens the report into the
main frame , the drill downs do not work and all images are broken.
If I then change the links to open to a new window (calling the URL
access directly), the reports render fine with drilldowns and images.
If I then point the links back to the frames, the reports render just
fine (drill down and all).
Here's the kicker..the drilldowns are expanded to the same positions I
used when I opened it to a new window.
That makes me think that there may be some session action setup by the
reportserver going on that I'm missing. When I close the browser, I
have to start that process over again.
Any thoughts?
Eugene Kolovyansky
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> Please take a look at Report Manager, which is part of the package. Report
> Manager is a separate ASP.NET application, just like yours. It uses SOAP
> calls to the server for management but it uses URL access and IFrame to
> render reports. See if drill down works in ReportManager. If it does, you
> should be able to do it the same way. If not, we can investigate this
> problem.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "Euge" <yevger@.yahoo.com> wrote in message
> news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > I've been through a good bit of the message boards trying to find an
> > answer to this problem.
> > I have an application that uses an iframe to embed a MSRS report. I'm
> > using URL access to call the report.
> > The problem is that any drill downs in the report DO NOT actually
> > expand anything in the report. I've looked at the rc:ReplacementRoot
> > in some threads but I don't see any differences with that extra
> > parameter.
> > Has anyone gotten a drilldown to work on an embedded report?
> >
> > FYI, I've tryied using the sample ReportViewer sample application as
> > well and I have the same problem:drill downs don't work. (Makes sense
> > since the ReportViewer sample just creates an iframe.)
> > Tested with both MSRS with and without service pack 1.
> >
> > Thanks,
> > Eugene Kolovyansky|||I found a way to get the reports to work with drilldowns and all. I
provide a sessionid to the report via rs:SessionId=<anything> with the
URL access url for the report. (as long as <anything> is
alpha-numeric)
I'm looking into sessions in reporting services to see what, if
anything, I would break by doing this.
Eugene Kolovyansky
yevger@.yahoo.com (Euge) wrote in message news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> Thanks for the quick response.
> I've looked a little at how the Report Manager handles reports. Drill
> downs work just fine there. I've noticed a strange issue with drill
> downs and images while pursuing this.
> Let's say I have a page with two frames.Menu frame points all links to
> the main frame. If I have a single link that opens the report into the
> main frame , the drill downs do not work and all images are broken.
> If I then change the links to open to a new window (calling the URL
> access directly), the reports render fine with drilldowns and images.
> If I then point the links back to the frames, the reports render just
> fine (drill down and all).
> Here's the kicker..the drilldowns are expanded to the same positions I
> used when I opened it to a new window.
> That makes me think that there may be some session action setup by the
> reportserver going on that I'm missing. When I close the browser, I
> have to start that process over again.
> Any thoughts?
> Eugene Kolovyansky
> "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > Please take a look at Report Manager, which is part of the package. Report
> > Manager is a separate ASP.NET application, just like yours. It uses SOAP
> > calls to the server for management but it uses URL access and IFrame to
> > render reports. See if drill down works in ReportManager. If it does, you
> > should be able to do it the same way. If not, we can investigate this
> > problem.
> >
> > --
> > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > --
> >
> > ---
> > "Euge" <yevger@.yahoo.com> wrote in message
> > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > I've been through a good bit of the message boards trying to find an
> > > answer to this problem.
> > > I have an application that uses an iframe to embed a MSRS report. I'm
> > > using URL access to call the report.
> > > The problem is that any drill downs in the report DO NOT actually
> > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > in some threads but I don't see any differences with that extra
> > > parameter.
> > > Has anyone gotten a drilldown to work on an embedded report?
> > >
> > > FYI, I've tryied using the sample ReportViewer sample application as
> > > well and I have the same problem:drill downs don't work. (Makes sense
> > > since the ReportViewer sample just creates an iframe.)
> > > Tested with both MSRS with and without service pack 1.
> > >
> > > Thanks,
> > > Eugene Kolovyansky|||Drill downs as well as images depend on sessions to work properly. Session
ID is stored in a cookie and set when you first run the report (or press
Ctrl+F5). If your cookies are not shared across frames you get different
sessions.
Providing session ID on the URL forces server to use cookieless sessions.
Session ID that you provide on URL is not present on the server, so server
starts a new session. Since it is a cookieless session, server redirects you
to the new URL with correct session ID (instead of just setting cookie).
Hope this explains the behavior you are seeing.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Euge" <yevger@.yahoo.com> wrote in message
news:3f8e8a3d.0408020626.3b640531@.posting.google.com...
> I found a way to get the reports to work with drilldowns and all. I
> provide a sessionid to the report via rs:SessionId=<anything> with the
> URL access url for the report. (as long as <anything> is
> alpha-numeric)
> I'm looking into sessions in reporting services to see what, if
> anything, I would break by doing this.
> Eugene Kolovyansky
> yevger@.yahoo.com (Euge) wrote in message
news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> > Thanks for the quick response.
> > I've looked a little at how the Report Manager handles reports. Drill
> > downs work just fine there. I've noticed a strange issue with drill
> > downs and images while pursuing this.
> >
> > Let's say I have a page with two frames.Menu frame points all links to
> > the main frame. If I have a single link that opens the report into the
> > main frame , the drill downs do not work and all images are broken.
> >
> > If I then change the links to open to a new window (calling the URL
> > access directly), the reports render fine with drilldowns and images.
> > If I then point the links back to the frames, the reports render just
> > fine (drill down and all).
> > Here's the kicker..the drilldowns are expanded to the same positions I
> > used when I opened it to a new window.
> >
> > That makes me think that there may be some session action setup by the
> > reportserver going on that I'm missing. When I close the browser, I
> > have to start that process over again.
> >
> > Any thoughts?
> > Eugene Kolovyansky
> >
> > "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > > Please take a look at Report Manager, which is part of the package.
Report
> > > Manager is a separate ASP.NET application, just like yours. It uses
SOAP
> > > calls to the server for management but it uses URL access and IFrame
to
> > > render reports. See if drill down works in ReportManager. If it does,
you
> > > should be able to do it the same way. If not, we can investigate this
> > > problem.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > > --
> > >
> > > ---
> > > "Euge" <yevger@.yahoo.com> wrote in message
> > > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > > I've been through a good bit of the message boards trying to find an
> > > > answer to this problem.
> > > > I have an application that uses an iframe to embed a MSRS report.
I'm
> > > > using URL access to call the report.
> > > > The problem is that any drill downs in the report DO NOT actually
> > > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > > in some threads but I don't see any differences with that extra
> > > > parameter.
> > > > Has anyone gotten a drilldown to work on an embedded report?
> > > >
> > > > FYI, I've tryied using the sample ReportViewer sample application as
> > > > well and I have the same problem:drill downs don't work. (Makes
sense
> > > > since the ReportViewer sample just creates an iframe.)
> > > > Tested with both MSRS with and without service pack 1.
> > > >
> > > > Thanks,
> > > > Eugene Kolovyansky|||Hi Eugene,
Did u happen to find find any problems with stating the sessionid=<anything>
Thanks
"Euge" wrote:
> I found a way to get the reports to work with drilldowns and all. I
> provide a sessionid to the report via rs:SessionId=<anything> with the
> URL access url for the report. (as long as <anything> is
> alpha-numeric)
> I'm looking into sessions in reporting services to see what, if
> anything, I would break by doing this.
> Eugene Kolovyansky
> yevger@.yahoo.com (Euge) wrote in message news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> > Thanks for the quick response.
> > I've looked a little at how the Report Manager handles reports. Drill
> > downs work just fine there. I've noticed a strange issue with drill
> > downs and images while pursuing this.
> >
> > Let's say I have a page with two frames.Menu frame points all links to
> > the main frame. If I have a single link that opens the report into the
> > main frame , the drill downs do not work and all images are broken.
> >
> > If I then change the links to open to a new window (calling the URL
> > access directly), the reports render fine with drilldowns and images.
> > If I then point the links back to the frames, the reports render just
> > fine (drill down and all).
> > Here's the kicker..the drilldowns are expanded to the same positions I
> > used when I opened it to a new window.
> >
> > That makes me think that there may be some session action setup by the
> > reportserver going on that I'm missing. When I close the browser, I
> > have to start that process over again.
> >
> > Any thoughts?
> > Eugene Kolovyansky
> >
> > "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > > Please take a look at Report Manager, which is part of the package. Report
> > > Manager is a separate ASP.NET application, just like yours. It uses SOAP
> > > calls to the server for management but it uses URL access and IFrame to
> > > render reports. See if drill down works in ReportManager. If it does, you
> > > should be able to do it the same way. If not, we can investigate this
> > > problem.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > --
> > >
> > > ---
> > > "Euge" <yevger@.yahoo.com> wrote in message
> > > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > > I've been through a good bit of the message boards trying to find an
> > > > answer to this problem.
> > > > I have an application that uses an iframe to embed a MSRS report. I'm
> > > > using URL access to call the report.
> > > > The problem is that any drill downs in the report DO NOT actually
> > > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > > in some threads but I don't see any differences with that extra
> > > > parameter.
> > > > Has anyone gotten a drilldown to work on an embedded report?
> > > >
> > > > FYI, I've tryied using the sample ReportViewer sample application as
> > > > well and I have the same problem:drill downs don't work. (Makes sense
> > > > since the ReportViewer sample just creates an iframe.)
> > > > Tested with both MSRS with and without service pack 1.
> > > >
> > > > Thanks,
> > > > Eugene Kolovyansky
>

Sunday, March 25, 2012

Droppin rule in SQL Server 2000

Droppin rule in SQL Server 2000

Thursday, March 22, 2012

Drop User and schema

Wednesday, March 21, 2012

DROP TABLE failed

Drop table causes system to hang

Monday, March 19, 2012

drop manually table (on the subscriber) that was part of merge p

Wednesday, March 7, 2012

Drop all the connections to the DataBase

Drop all the connections to the DataBase

Sunday, February 26, 2012

Driver''s SQLSetConnectAttr failed from script component

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

Driver's SQLAllocHandle on SQL_HANDLE_DBC failed error

Driver caching db?

Driver caching db?

Friday, February 24, 2012

Driver

Driver

Tuesday, February 14, 2012

Drill down using url access in ASP.net not working

DSO in SQL2005

Blog Archive

About Me