DSO in SQL2005: users

Showing posts with label users. Show all posts

Sunday, March 25, 2012

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett Wickard
Hi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Dropped connections, but open on the SQL Server...

I have a frustrating issue that hopefully someone can help with.
I have a few remote users that connect to the sql server through 1433 and
sql authentication (the ISA firewall only allows their IP addresses to
connect). They connect through an ODBC connection with no Idle timeout.
These users are getting their connections closed but I look at the EM and
the connection is still open. If I have them connect via VPN first,
everything works fine - the connection is never closed. Do you know why a
standard ODBC connection would close itself only if it's not behind a VPN?
I can't figure it out for the life of me!! There must be some timeout
parameter somewhere that I have set, but I can't find it...
Any ideas would be greatly appreciated!
Regards,
Brett WickardHi Brett,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to close ODBC
connection when the user terminate the connection. If I have misunderstood
your concern, please feel free to point it out.
First of all, please use sp_who in the Query Analyzer to make sure the
connnection from these remote customer remain alive.
Secondly, how does your customer connect to SQL Server? If you utilize ADO
in your VB application to access the SQL Server, please try the following
sample code.
--
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
cn.ConnectionTimeout = 45
cmd.CommandTimeout = 15
cn.close
--
For more information, please refer to Microsoft SQL Server Books Online
with the keywords 'ADO' and 'connectiontimeout' or 'commandtimeout' as the
search topics.
Last but not the least, the following KB describes how to define the
orphaned connection and set OLE DB Provider for ODBC's timeout settings.
INFO: OLE DB Session Pooling Timeout Configuration
http://support.microsoft.com/kb/Q237977
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/kb/Q137983
INFO: Frequently Asked Questions About ODBC Connection Pooling
http://support.microsoft.com/kb/Q169470
Resource Pooling
http://msdn.microsoft.com/library/d...-us/oledb/htm/o
ledbresourcepooling.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Thursday, March 22, 2012

drop users connected to a database

hi there,
any sql code example out there to do this?
thanks,
PaulLookup KILL command in the BOL
"Milsnips" <milsnips@.hotmail.com> wrote in message
news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
> hi there,
> any sql code example out there to do this?
> thanks,
> Paul
>|||Do you want to drop logins (i.e. prevent the users from accessing the server
)?
Do you want to deny the users access to a specific database?
Do you want to disconnect user sessions?
Which is it? What version of SQL Server are you using?
Look up in Books Online:
1) sp_droplogin to drop logins in SQL 2000, or the DROP LOGIN statement for
SQL 2005;
2) sp_revokedbaccess to deny access to a SQL 2000 database, or the DENY
statement for SQL 2005;
3) kill to disconnect individual processes (identify them first with sp_who
or sp_who2).
ML
http://milambda.blogspot.com/|||alter database <dbname> set single_user with rollback immediate
The rollback immediate option will automatically terminate all sessions and
rollback any active transactions, so it's basically the nuclear option. Hee
Heee ;-)
http://msdn.microsoft.com/library/d...>
_03_725v.asp
"Milsnips" <milsnips@.hotmail.com> wrote in message
news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
> hi there,
> any sql code example out there to do this?
> thanks,
> Paul
>|||Don=B4t know why you want to drop them, but if you want to do something
adminstrative could go by this:
ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate
<DoSomethingAdministrative>
ALTER DATABASE <Nameofthedb> SET=20
MULTI_USER=20
HTH, Jens Suessmeyer.|||I'd check if any of the users carry guns before trying that. :)
ML
http://milambda.blogspot.com/|||Well, I send out a group wide email notification ahead of time. If it's past
5:00pm, most users are looking for an excuse to just pack up and go home
anyway. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:22239C1E-F70B-4D46-9A1D-C72D342A1502@.microsoft.com...
> I'd check if any of the users carry guns before trying that. :)
>
> ML
> --
> http://milambda.blogspot.com/|||I don't think he wants to actually drop their user id from the database;
just kill the connection.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1134048213.022697.236960@.z14g2000cwz.googlegroups.com...
Dont know why you want to drop them, but if you want to do something
adminstrative could go by this:
ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate
<DoSomethingAdministrative>
ALTER DATABASE <Nameofthedb> SET
MULTI_USER
HTH, Jens Suessmeyer.|||Actually the rollback normal with time-out would probably be more
appropriate, if you can give them 1/2 hour to wrap things up.
"JT" <someone@.microsoft.com> wrote in message
news:%23SVlYl$%23FHA.3632@.TK2MSFTNGP10.phx.gbl...
> alter database <dbname> set single_user with rollback immediate
> The rollback immediate option will automatically terminate all sessions
> and rollback any active transactions, so it's basically the nuclear
> option. Hee Heee ;-)
> http://msdn.microsoft.com/library/d...
es_03_725v.asp
>
> "Milsnips" <milsnips@.hotmail.com> wrote in message
> news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
>|||Well, I guess it's about that time wherever the OP is. :)
ML
http://milambda.blogspot.com/sql

Drop Users

I know that there is a way to drop a user from one database but not from another. Does sp_drop user do that? To be more specific, I want to drop all users (except for dbo) from one database but not from all others. Is that possible.If you just want to revoke access for a specific database you can run sp_revokedbaccess 'Username'. You must have selected the database in Query Analyzer first.

/ Jonas|||Be sure to execute in quary analyzer and in the database from where you want to drop the users.

declare @.user_name as varchar(100)

DECLARE CUsers CURSOR FOR
SELECT name FROM MyTable..sysusers
where name <> 'dbo'

OPEN CUsers
FETCH FROM CUsers INTO @.User_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_dropuser @.User_Name
FETCH NEXT FROM CUsers INTO @.User_Name
END

Wednesday, March 21, 2012

Drop table

Hi NG,
We have a development database and from time to time some user or users will
issue a drop table and I would like to know the user who drop the table.
Is there a way to determine who and when the user issues the drop table DDL
without running the profiler?
If it can't be done without using profiler, how do I filter it by drop table
only if I'm running the profiler?
If you know of any third party software please include it also in your
reply.
Thank you in advance.Hi,
Which version of SQL are you using?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
> Hi NG,
> We have a development database and from time to time some user or users
> will
> issue a drop table and I would like to know the user who drop the table.
> Is there a way to determine who and when the user issues the drop table
> DDL
> without running the profiler?
> If it can't be done without using profiler, how do I filter it by drop
> table
> only if I'm running the profiler?
> If you know of any third party software please include it also in your
> reply.
> Thank you in advance.
>|||SQL Server 2000
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:uQbJcWK$FHA.2740@.tk2msftngp13.phx.gbl...
> Hi,
> Which version of SQL are you using?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
> > Hi NG,
> >
> > We have a development database and from time to time some user or users
> > will
> > issue a drop table and I would like to know the user who drop the table.
> >
> > Is there a way to determine who and when the user issues the drop table
> > DDL
> > without running the profiler?
> >
> > If it can't be done without using profiler, how do I filter it by drop
> > table
> > only if I'm running the profiler?
> >
> > If you know of any third party software please include it also in your
> > reply.
> >
> > Thank you in advance.
> >
> >
>|||Hi,
You should use profiler in this case. In SQL 2005 there are DDL triggers you
could use.
For filtering you could filter on Textdata data column with %DROP TABLE% as
a filter. This way you'll see just DROPs of tables and of course you should
include LoginName or DBUserName data columns to see which user issued ta
command.
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:eNhYQhK$FHA.2944@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:uQbJcWK$FHA.2740@.tk2msftngp13.phx.gbl...
>> Hi,
>> Which version of SQL are you using?
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
>> news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
>> > Hi NG,
>> >
>> > We have a development database and from time to time some user or users
>> > will
>> > issue a drop table and I would like to know the user who drop the
>> > table.
>> >
>> > Is there a way to determine who and when the user issues the drop table
>> > DDL
>> > without running the profiler?
>> >
>> > If it can't be done without using profiler, how do I filter it by drop
>> > table
>> > only if I'm running the profiler?
>> >
>> > If you know of any third party software please include it also in your
>> > reply.
>> >
>> > Thank you in advance.
>> >
>> >
>>
>|||"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should use profiler in this case. In SQL 2005 there are DDL triggers
> you could use.
In 2005 you don't even need DDL triggers. The lightweight always-on trace
will catch this, and the "Schema Changes History" report in Management
Studio will show you who did what.
Now, DDL triggers could be used to capture more details, or actually prevent
the table from being dropped...
David|||I believe that is what the other reference was for. Just know the small
trace is not a permanent log, it rolls over.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uTGN%23BN$FHA.504@.TK2MSFTNGP12.phx.gbl...
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> You should use profiler in this case. In SQL 2005 there are DDL triggers
>> you could use.
> In 2005 you don't even need DDL triggers. The lightweight always-on trace
> will catch this, and the "Schema Changes History" report in Management
> Studio will show you who did what.
> Now, DDL triggers could be used to capture more details, or actually
> prevent the table from being dropped...
> David
>|||Hi Andy,
Do you know any method to automatically save the contents of that trace file?
Something like a sheduled task or SQL Job?
Thanx,
Sorin
"Andy Wilbourn" wrote:
> I believe that is what the other reference was for. Just know the small
> trace is not a permanent log, it rolls over.
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uTGN%23BN$FHA.504@.TK2MSFTNGP12.phx.gbl...
> >
> > "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> > news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >>
> >> You should use profiler in this case. In SQL 2005 there are DDL triggers
> >> you could use.
> >
> > In 2005 you don't even need DDL triggers. The lightweight always-on trace
> > will catch this, and the "Schema Changes History" report in Management
> > Studio will show you who did what.
> >
> > Now, DDL triggers could be used to capture more details, or actually
> > prevent the table from being dropped...
> >
> > David
> >
>
>

Monday, March 19, 2012

Drop subscription locking users

Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!
|||The new publication is on the same server, same database, the subscriber is a new server, so in essence your assumption is correct. I want to drop the old publication on the existing publishing server/database, since the subscribing server will be retired.
Ok, so I need to drop my articles on this publication one by one. Ugh! That's 1600+ articles.
Thanx for the answer.
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:%23KCcOfJ5FHA.2432@.TK2MSFTNGP10.phx.gbl...
From what you have described below, it would appear that you were dropping the last subscription on the old publisher database (I am guessing here... the new publication is on a different server right?) What happens in this case is that the "replicated" bits on the published tables are reset to 0 which, unfortunately, is considered a schema change by the server and thus requiring the use of sch-mod lock on the published table. When you drop a subscription through SEM, sp_dropsubscription is called with @.article = 'all' which would in turn cause sch-mod lock to be obtained for all published tables in the publication. Obviously, this is not something that is easily achievable when there are concurrent activities at the publisher (the old one you have) database. One way to workaround this is to drop subscription one article at a time by manually calling sp_dropsusbcription within a cursor through the list of article names that you have in your publication.
Things get a bit more complicated if your publication has the immediate_sync property set to 1 (a requirement for allowing anonymous subscriptions) as the "last" subscription on the publication is actually the "virtual" subscription that we create for you automatically and the virtual subscription will not be dropped unless the articles in your publication are dropped. So, if your subscription has the immediate_sync property set to 1, you would need to drop articles one by one after dropping your subscription to avoid sch-mod locks being taken simultaneously for all published tables in your publication.
Hope that helps.
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eIaHflI5FHA.156@.TK2MSFTNGP15.phx.gbl...
Why would a delete of a subscription & publication lock users in the database?
It is a large publication, but when I looked at the activity it was doing a sp_dropsubscription, and I don't understand why this locks the users out of the tables.
What can I do to drop this old subscription & publication?
The new publication & subscription are up on the new server, but I want to delete the old without locking the users, how?
Thanx!

Sunday, March 11, 2012

Drop Down list with many elements

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

1. Create a summary page where you have a parameter which will search for productName (say we call it @.searchProduct) and generate a list of products related to that name. In SQL you can do (where productName like '% + @.searchProduct + %')

2. Then if the user click on a product it will take you to another report where you have all other parameters.

But the good thing is you don't have to worry about the product because you already selected your product and passed the productID from report1 to report2. Report2 is basically the same report you are working on but without the parameter for productID. You can make it hidden.

Feel free to ask me more questions...

|||

I really like your idea and I'm very curious to see how to implement it. Would you mind sending me an example by email (an rdl file for example)? I' ve tried something like that in the past, but I've never succeeded to do it... :-(

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

For exmple if you are using table to output the products then right click on the detail textbox and go to properties and then click on navigation. There you can select jump to a report, select the report you want to jump to and pass productId as a parameter. Now when you run the report you will be able to click on the products and it will take you to the next report that you specified in the properties.

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Drop Down list with many elements

Hello,

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

Did you create the drop down list using visual studio 2005?

You may want to divide this HUGE dropdownlist into maybe 26 dropdown lists -- categorize them by first letter in the product.

Only populate the A dropdown list with products that start with A, etc.

Then, handle each dropdown list's selection changed event separately. (I would imagine that each event would have the same code though)

Or maybe 26 is too many. Just do one dropdown for the first half of the alphabet and then another for the second half.

You get the idea though: divide and conquer.

|||I like the idea of conquering :-)
Your idea is good but I alreday have many drop down lists because I use cascading parameters (8!). Then it would become too heavy visually...

Any other solution?
Thanks.

PS: Yes, I used Visual Studio 2005.
|||

You can have a muti-page report.

The idea is to create a parameter called "Search Product" which takes string as an argument.

Then output products based on the value of the parameter.

Then click on the product which will take you to your current report.

Thanks,

-Rohit

|||Hi,

Can you detail a little bit more? I am not sure to understand what you mean...
Do you mean that I should create a search field?

Thanks.
Zoz
|||

See what I am trying to say is:

2. Then if the user click on a product it will take you to another report where you have all other parameters.

Feel free to ask me more questions...

|||

For the problem I popsted previously, your solution doesn't meet the requirements because users need to choose several products...

I tried to implement cascading parameters although it is not the best one in terms of performance... So I'm still looking for a better idea.

Thanks

|||

It is very simple....

-Rohit

|||

Thanks for your answer! Looks like it is a great idea! I'll try it!

And is there any way to have a "select all" ? ( it is also requiered by my users...)

Thanks

Zoz

Friday, March 9, 2012

Drop Database

I want to drop a database but there are still users online from several
computers.
Is there any way of deleting it?You can kill the Processes of the users associated with the database within
a Cursor.
Information about Running processes are present within the Proc sp_who.
Processes can be killed with KILL Number.
Drop the table afterwards the killing.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Joaquim Meireles" <jmeireles@.lognet.pt> schrieb im Newsbeitrag
news:O33JqyAQFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I want to drop a database but there are still users online from several
>computers.
> Is there any way of deleting it?
>|||Use the ALTER DATABASE command to set the database to single user, restricte
d user or off line. Also
use the ROLLBACK option to kick out current users. ALTER DATABASE is documen
ted in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joaquim Meireles" <jmeireles@.lognet.pt> wrote in message
news:O33JqyAQFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I want to drop a database but there are still users online from several com
puters.
> Is there any way of deleting it?
>|||You can set the database to SINGLE_USER mode with the ROLLBACK option to
forcibly kill existing users:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE MyDatabase
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Joaquim Meireles" <jmeireles@.lognet.pt> wrote in message
news:O33JqyAQFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I want to drop a database but there are still users online from several
>computers.
> Is there any way of deleting it?
>

Sunday, February 26, 2012

Driver questions running with DB2 as datasource

I need some help and feedbacks from other users that are currently running Reporting Services using DB2 as a database. I have been testing OLE DB and ODBC from Microsoft and IBM, and recently DB2 .Net Data provider from IBM.

We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.

First I installed IBM DB2 ODBC DRIVER v. 7.01.00.88, but I did not get this too work with reporting Services calling Stored Procedures, so I had too upgrade the driver to v8.01.10.812 to get it to work.

I have been in contact with Microsoft and they recommend using Microsoft OLE DB Provider for DB2 (30.sept 2005). I have tried too get this driver too work but I have problems configuring it. I think that it has something too do with some packages that the driver tries too create on DB2. I get error messages both on the DB2 and in the Data access Tool that runs a wizard setting up the driver. '—The package creation process has failed on resource "mytest", Data description mismatch SQLSTATE:HY000, SQLCODE: -292'

IBM have also developed a IBM DB2 Data provider for .Net Framework 2.0. This is the only driver that I can use too browse the database in the Server Explorer in VS. If you edit the RSReportDesigner.config file you can also use this driver in Report Server projects. Just add <Extension Name="DB2" Type="IBM.Data.DB2.DB2Connection,IBM.Data.DB2, Version=9.0.0.2, Culture=neutral, PublicKeyToken=7c307b91aa13d208" /> too the data section of the xml file.

So too wrap it all up. Which provider should I use? Are there any recommendations out there?

Thanks

well i've a question.

how to get IBM DB2 Data Provider for .Net Framework 2.0

Driver questions running with DB2 as datasource

We are currently running DB2 on z/os v 7, gateway v.8 fixpack 10. We will mainly be calling Stored Procedures on the DB2 and passing recordsets back to the client.

So too wrap it all up. Which provider should I use? Are there any recommendations out there?

Thanks

well i've a question.

how to get IBM DB2 Data Provider for .Net Framework 2.0

Sunday, February 19, 2012

DrillThrough in Excel 2000

Hi all
I needed to provide OLAP drillthough for users with Excel 2000. There
are numerous threads on this topic - but no-one seems to have produced
the definitive work around.
So I made one myself and here it is. I based it on the extending OLAP
solution originally published on MSDN. It works most of the time, and
assumes that dimension members are not duplicated across levels.
Use this as a standalone workbook, an add-in or in a report workbook.
Paste this code into the code area of ThisWorkbook.
Add references to ADO and ADOMD.
I hope someone finds it useful.
Ian Bamforth
-- the code --
' This VBA macro is a modified version of an MSDN sample. The sample
was contained
' in an article titled "Extending Excel OLAP Functionality"
'
' Russ Whitney
' ProClarity Corporation
' August 15, 2003
'
'Minor Tweak IPB Sept 04 to work as standalone
'
'Rewite for Excel 2000 ipb March 20 2005 (removed PivotCell and
ADOconnection objects)
' Runs when the workbook is opened
Private Sub Workbook_Open()
Dim oPTCmdBar As CommandBar
Dim oPTCmdBarCntrl As CommandBarControl
Dim oPTDrillCmd As CommandBarControl
' Get a reference to the PivotTable's context menu
Set oPTCmdBar = Application.CommandBars("PivotTable context menu")
Set oPTDrillCmd = Nothing
' Check all the items in the context menu to see if we already
added our item
For Each oPTCmdBarCntrl In oPTCmdBar.Controls
If oPTCmdBarCntrl.Caption = "Drill to details2k" Then
Set oPTDrillCmd = oPTCmdBarCntrl
Exit For
End If
Next oPTCmdBarCntrl
' If our drill menu option was not already in the context menu then
add it
If oPTDrillCmd Is Nothing Then
Set oPTDrillCmd =
oPTCmdBar.Controls.Add(Type:=msoControlButton, temporary:=True)
oPTDrillCmd.Caption = "Drill to details2k"
End If
' Regardless of whether the item was on the menu or not, make sure
it runs the
' Drillthrough routine when it is selected
oPTDrillCmd.OnAction = "ThisWorkbook.Drillthrough2k"
End Sub
Private Function CreateDrillMdx2k(oCell As Range) As String
Dim sDrillMdx As String
Dim i As Integer
Dim iAxisNum As Integer
Dim iRowCol As Integer
Dim Mrow As Range, Mcol As Range
Dim McolLabel As String, MrowLabel As String
' The start of the query
sDrillMdx = "DRILLTHROUGH MAXROWS 1000 SELECT "
' Determine the dimension members on the row and column headers
matching this cell
'...take the row label adjacent to the data area
Set Mrow = Cells(oCell.Row, oCell.PivotTable.DataBodyRange.Column -
1)
'... and pick from the previous column if it is empty (ie the item
was drilled down) - allow up to 4 levels
For i = 0 To 3
McolLabel = Mrow.Offset(0, -i)
If McolLabel <> "" Then Exit For
Next
'...remove the word Total from the end of the label
If Right(McolLabel, 6) = " Total" Then McolLabel = Left(McolLabel,
Len(McolLabel) - 6)
'...repeat for the column label
Set Mcol = Cells(oCell.PivotTable.DataBodyRange.Row - 1,
oCell.Column)
For i = 0 To 3
MrowLabel = Mcol.Offset(-i, 0)
If MrowLabel <> "" Then Exit For
Next
If Right(MrowLabel, 6) = " Total" Then MrowLabel = Left(MrowLabel,
Len(MrowLabel) - 6)
'assume that the row and column labels are unique members of
dimensions
sDrillMdx = sDrillMdx & "{[" & MrowLabel & "]} ON " & 0 & ", "
sDrillMdx = sDrillMdx & "{[" & McolLabel & "]} ON " & 1 & ", "
iAxisNum = 2
Dim oPT As PivotTable
Dim pf As PivotField
Set oPT = oCell.PivotTable
'Set oPageFields = oPT.PageFields
' Add the member names for any paged dimensions
For Each pf In oPT.PageFields
sDrillMdx = sDrillMdx & "{" & pf.CurrentPageName & "} ON " &
iAxisNum & ", "
iAxisNum = iAxisNum + 1
Next
' Trim off the extra comma left by the last item appended to the
query
sDrillMdx = Left$(sDrillMdx, Len(sDrillMdx) - 2)
' Now add the cube name in the FROM clause
sDrillMdx = sDrillMdx & " FROM [" & oPT.PivotCache.CommandText &
"]"
' Return the MDX statement
CreateDrillMdx2k = sDrillMdx
End Function
Public Sub Drillthrough2k()
On Error GoTo errh
Dim oCell As Range
Dim opTItem As PivotItem
Dim oPT As PivotTable
Dim oOlapConn As New ADODB.Connection
Dim sDrillMdx As String
Dim oRecordSet As New ADODB.Recordset
Dim oSheet As Worksheet
Dim oQueryTable As QueryTable
Dim Mdrill As Boolean
Set oCell = ActiveCell
If WorksheetFunction.IsText(oCell) Then MsgBox "You should choose a
number that you wish to drillThrough": Exit Sub
Set oPT = oCell.PivotTable
'Set oOlapConn = oPT.PivotCache.ADOConnection
'NOT available in Excel 2K so we create new
'resolve ADO connection string by removing "OLAP;" from pivot cache
connection
oOlapConn.ConnectionString = Mid(oPT.PivotCache.Connection, 7, 150)
' Create the MDX Drillthrough statement
sDrillMdx = CreateDrillMdx2k(oCell)
oOlapConn.Open
' Execute the Drillthrough statement to get a recordset
oRecordSet.Source = sDrillMdx
oRecordSet.ActiveConnection = oOlapConn
oRecordSet.Open
' Create a new worksheet and add the drillthrough results
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Name = "DrillThrough" Then
Mdrill = True
oSheet.Activate
Cells(ActiveCell.SpecialCells(xlLastCell).Row + 2, 1).Activate
Exit For
End If
Next
If Not (Mdrill) Then
Set oSheet = ActiveWorkbook.Sheets.Add
oSheet.Name = "DrillThrough"
End If
ActiveCell = sDrillMdx
Set oQueryTable = oSheet.QueryTables.Add(oRecordSet,
ActiveCell.Cells(2, 1))
oQueryTable.Refresh
Exit Sub
errh:
MsgBox Error, vbInformation
End SubIan,
Thanks! Works well.
I have a question that will show my ignorance of cubes. Does doing a
"drillthrough" take you to the details stored in the cube, or does it
take you all the way back to the data warehouse?
TIA
JOHolloway
bammers99 wrote:
> Hi all
> I needed to provide OLAP drillthough for users with Excel 2000.
There
> are numerous threads on this topic - but no-one seems to have
produced
> the definitive work around.
> So I made one myself and here it is. I based it on the extending
OLAP
> solution originally published on MSDN. It works most of the time,
and
> assumes that dimension members are not duplicated across levels.
> Use this as a standalone workbook, an add-in or in a report workbook.
> Paste this code into the code area of ThisWorkbook.
> Add references to ADO and ADOMD.
> I hope someone finds it useful.
> Ian Bamforth|||Hi John
All the way back to the warehouse ...
- without needing to grant users logon rights to the SQL database
- In the Analysis Manager dialog, you can see all the columns in the
tables participating in the cube - and add others! You are not simply
resticted to fields assigned to measures and dimensions
misterholloway@.yahoo.com wrote:[vbcol=seagreen]
> Ian,
> Thanks! Works well.
> I have a question that will show my ignorance of cubes. Does doing a
> "drillthrough" take you to the details stored in the cube, or does it
> take you all the way back to the data warehouse?
> TIA
>
> JOHolloway
>
> bammers99 wrote:
> There
> produced
> OLAP
> and
workbook.[vbcol=seagreen]|||Ok John:
But you need to define the drill-throught query and options in the Analysis
Services... no?
Thanks
Rodrigo
"bammers99" wrote:

> Hi John
> All the way back to the warehouse ...
> - without needing to grant users logon rights to the SQL database
> - In the Analysis Manager dialog, you can see all the columns in the
> tables participating in the cube - and add others! You are not simply
> resticted to fields assigned to measures and dimensions
>
> misterholloway@.yahoo.com wrote:
> workbook.
>

Tuesday, February 14, 2012

drill through on chart working on some IE browsers and not on other browsers

I am having a peculiar problem. Drill through on Charts is not working on certain users machines. Drill through on tables is however working on all the user machines.

I checked IE security setting on users machines. But could not figure out what is causing the problem. Some one please help.

Regards

Bobba.

Hi,

The drillthrough functionality uses JavaScript. You need to check what the permissions are. Check if the security zone is the same for the working and not working pc's.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Drill Down Reports Permissions Problem

Hi,
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Sunday, March 25, 2012

Dropped connections, but open on the SQL Server...

Dropped connections, but open on the SQL Server...

Thursday, March 22, 2012

drop users connected to a database

Drop Users

Wednesday, March 21, 2012

Drop table

Drop table

Drop table

Monday, March 19, 2012

Drop subscription locking users

Sunday, March 11, 2012

Drop Down list with many elements

Drop Down list with many elements

Friday, March 9, 2012

Drop Database

Sunday, February 26, 2012

Driver questions running with DB2 as datasource

Driver questions running with DB2 as datasource

Sunday, February 19, 2012

DrillThrough in Excel 2000

Tuesday, February 14, 2012

drill through on chart working on some IE browsers and not on other browsers

Drill Down Reports Permissions Problem

DSO in SQL2005

Blog Archive

About Me