Showing posts with label own. Show all posts
Showing posts with label own. Show all posts

Tuesday, March 27, 2012

Bind my own DataSet that I get in my WebApp and show in the Reporting Services - is it pos

All,
Does anybody know if I can bind my own DataSet dynamically getting it in my
WebApp and show it in the Reporting Services using the services as a
formatter/viewer of this DataSet - is it possible?
Or all I can do with the RS 2000 is just a new VD for each new report and I
have to use this URL to show anything from my app? I didn't see anything
helpful allowing us to integrate the Reporting Services into WebApp yet on
the fly. What's the difference if the RS generates the DataSet or it's
generated by my application? The second way is much more flexible and
practical. Is it already implemented in the RS 2005? I saw the controls, but
didn't test them yet since my solution is not convertible to the VS2005 with
the new Reporting Services.
Just D.VS 2005 have two new controls. Webform and winform. You can use them in
local mode and give them the dataset. In local mode you do have more you
have to do (subforms etc).
In 2000 you would have to create a data processing extension (which would
work for either 2000 or 2005).
RS is designed as a service oriented application. You request a service and
RS implements it. You can call stored procedures easily or use SQL
statement. Pass in the parameters from your app and let RS create the
dataset.
Any particular reason you want to be passing a dataset. If you let RS handle
it then it will automatically do a lot for you. For instance, take the issue
of subreports. If you are handling the dataset then for each subreport you
need to respond to an event and generate the datasets for the subreport as
well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Just D." <no@.spam.please> wrote in message
news:Kk5If.13830$eR.455@.fed1read03...
> All,
> Does anybody know if I can bind my own DataSet dynamically getting it in
> my WebApp and show it in the Reporting Services using the services as a
> formatter/viewer of this DataSet - is it possible?
> Or all I can do with the RS 2000 is just a new VD for each new report and
> I have to use this URL to show anything from my app? I didn't see anything
> helpful allowing us to integrate the Reporting Services into WebApp yet on
> the fly. What's the difference if the RS generates the DataSet or it's
> generated by my application? The second way is much more flexible and
> practical. Is it already implemented in the RS 2005? I saw the controls,
> but didn't test them yet since my solution is not convertible to the
> VS2005 with the new Reporting Services.
> Just D.
>|||I've got a case where I need to bind to a dataset that I fetch. In my
case, I need to get a stored definition of a user-customized query, and
generate RDL from that definition. I then need to fill a weakly-typed
dataset to be used as the data source for the generated report.
Any reference on how to use pre-filled datasets in SRSS? I've been
searching Google and MSDN, and have so far come up short.
Thanks in advance.
Bruce L-C [MVP] wrote:
> VS 2005 have two new controls. Webform and winform. You can use them in
> local mode and give them the dataset. In local mode you do have more you
> have to do (subforms etc).
> In 2000 you would have to create a data processing extension (which would
> work for either 2000 or 2005).
> RS is designed as a service oriented application. You request a service and
> RS implements it. You can call stored procedures easily or use SQL
> statement. Pass in the parameters from your app and let RS create the
> dataset.
> Any particular reason you want to be passing a dataset. If you let RS handle
> it then it will automatically do a lot for you. For instance, take the issue
> of subreports. If you are handling the dataset then for each subreport you
> need to respond to an event and generate the datasets for the subreport as
> well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Just D." <no@.spam.please> wrote in message
> news:Kk5If.13830$eR.455@.fed1read03...
> > All,
> >
> > Does anybody know if I can bind my own DataSet dynamically getting it in
> > my WebApp and show it in the Reporting Services using the services as a
> > formatter/viewer of this DataSet - is it possible?
> >
> > Or all I can do with the RS 2000 is just a new VD for each new report and
> > I have to use this URL to show anything from my app? I didn't see anything
> > helpful allowing us to integrate the Reporting Services into WebApp yet on
> > the fly. What's the difference if the RS generates the DataSet or it's
> > generated by my application? The second way is much more flexible and
> > practical. Is it already implemented in the RS 2005? I saw the controls,
> > but didn't test them yet since my solution is not convertible to the
> > VS2005 with the new Reporting Services.
> >
> > Just D.
> >
> >|||If you are creating your own RDL then the way to go is to definitely get the
new controls with VS 2005. In local mode you don't need any server. You can
give the report the rdlc and the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kevin L." <kevin.lowe0@.gmail.com> wrote in message
news:1139862643.634896.4400@.o13g2000cwo.googlegroups.com...
> I've got a case where I need to bind to a dataset that I fetch. In my
> case, I need to get a stored definition of a user-customized query, and
> generate RDL from that definition. I then need to fill a weakly-typed
> dataset to be used as the data source for the generated report.
> Any reference on how to use pre-filled datasets in SRSS? I've been
> searching Google and MSDN, and have so far come up short.
> Thanks in advance.
>
> Bruce L-C [MVP] wrote:
>> VS 2005 have two new controls. Webform and winform. You can use them in
>> local mode and give them the dataset. In local mode you do have more you
>> have to do (subforms etc).
>> In 2000 you would have to create a data processing extension (which would
>> work for either 2000 or 2005).
>> RS is designed as a service oriented application. You request a service
>> and
>> RS implements it. You can call stored procedures easily or use SQL
>> statement. Pass in the parameters from your app and let RS create the
>> dataset.
>> Any particular reason you want to be passing a dataset. If you let RS
>> handle
>> it then it will automatically do a lot for you. For instance, take the
>> issue
>> of subreports. If you are handling the dataset then for each subreport
>> you
>> need to respond to an event and generate the datasets for the subreport
>> as
>> well.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Just D." <no@.spam.please> wrote in message
>> news:Kk5If.13830$eR.455@.fed1read03...
>> > All,
>> >
>> > Does anybody know if I can bind my own DataSet dynamically getting it
>> > in
>> > my WebApp and show it in the Reporting Services using the services as a
>> > formatter/viewer of this DataSet - is it possible?
>> >
>> > Or all I can do with the RS 2000 is just a new VD for each new report
>> > and
>> > I have to use this URL to show anything from my app? I didn't see
>> > anything
>> > helpful allowing us to integrate the Reporting Services into WebApp yet
>> > on
>> > the fly. What's the difference if the RS generates the DataSet or it's
>> > generated by my application? The second way is much more flexible and
>> > practical. Is it already implemented in the RS 2005? I saw the
>> > controls,
>> > but didn't test them yet since my solution is not convertible to the
>> > VS2005 with the new Reporting Services.
>> >
>> > Just D.
>> >
>> >
>

Thursday, February 16, 2012

Best way to make a database not in use?

Hi.

I am almost wrapping up a project of my own and I will have the option to restore the database, so dropping the existing one and then running the SQL script to create the database, tables, stored procedures and so on.

My question is, (using .NET 2.0 C#) - what is the best way to resolve the "cannot drop database as it is currently in use" message, or some message of the similar sort?

Even though I specified "USE MASTER" in the commandText string, there will be somewhere, in a situation, where the database will still be in use.

How can I forcefully make it NOT in use, drop it and do other things I like to?

What is the best way?

Many thanks!

Commonly we just cursor through open spids and execute KILL session_id/spid:

--2005
select session_id
from sys.dm_exec_requests
where db_name(database_id) = 'your database'

--2000 and earlier
select spid
from master..sysprocesses
where db_name(dbid) = 'your database'

|||Are you using SQL 2005? If so, in Management Studio, in Object Explorer, navigate to [Database Name]\Management\Acticity Monitor. Double click Activity Monitor - the Activity Monitor appears.

Using the Activity Monitor (with "Process Info" selected in the left-hand pane), you can see all active processes. As you have noticed, you won't be able to drop your database with active processes. Simply right-click processes that you wish to terminate, and choose "Kill Process". Note that you can see the database name as part of each process' information.

--

If you are using SQL 2000, (from my memory) an easy way to terminate all processes is to right-click the database, choose "Detach", and then when the dialog appears, there's an option to clear all existing users. After this has finished, you can drop the database.|||

Many thanks, I will give this a shot.

It's just amazing, how we can do ALL of this using SQL Server, kill processes, query the master tables as well as each of our own created tables/databases - fasinates me always :)

|||If you have any problems, or need to post again (SQL or otherwise), it's always helpful to specify product & the version of the software you are using. (SQL 2000 or SQL 2005, VB, VB.NET, C#, VS.NET 2003/2005 ... etc.)|||

Just:

use [YourDb]

alter database [YourDb] set single_user with rollback immediate

-- if there are no users with dbo permissions, you may use restricted_user instead

go

-- do your restore here

go

alter database [YourDb] set multi_user

|||gavrilenko_s - good suggestion. |||

ok, thanks for that

one more Q

if we have more than 1 SPID for a database, how can I kill them all?

I was thinking about doing this, however I think it's bad practice and prefer to do it the proper way!

DECLARE @.numberOfProcess int
SET @.numberOfProcess = 0
SET @.numberOfProcess = (select COUNT (spid) from master..sysprocesses where db_name(dbid) = 'db')
--SELECT @.numberOfProcess
IF (@.numberOfProcess > 0)
BEGIN
WHILE (@.numberOfProcess IS NOT NULL)
BEGIN
SET @.numberOfProcess = (select TOP 1 (spid) from master..sysprocesses where db_name(dbid) = 'db')
-- SELECT @.numberOfProcess
DECLARE @.SqlStatement NVARCHAR (20)
SET @.SqlStatement = 'KILL ' + CAST(@.numberOfProcess AS CHAR(2))
SELECT @.sqlStatement
EXEC sp_executesql @.SqlStatement
END

END


|||

Hi,

The method gavrilenko_s posted before is the best (which is always use for kicking users).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Tuesday, February 14, 2012

Best way to get TOP 10 CPU intensive SQL

I run a shared hosting server where multiple people share a single SQL
server (each of them have their own data base). I need a way to monitor to
find out what % of the resources each user is taking as we don't want any
one person abusing the system. What is the best way to do this
Jay
Jay
Run SQL Server Profiler tool.
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>
|||See if this helps:
Identifying SQL Server Perfromance Issues Using Profiler
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>

Monday, February 13, 2012

Best way to get TOP 10 CPU intensive SQL

I run a shared hosting server where multiple people share a single SQL
server (each of them have their own data base). I need a way to monitor to
find out what % of the resources each user is taking as we don't want any
one person abusing the system. What is the best way to do this
JayJay
Run SQL Server Profiler tool.
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>|||See if this helps:
Identifying SQL Server Perfromance Issues Using Profiler
http://vyaskn.tripod.com/analyzing_profiler_output.htm
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jay Janarthanan" <jay@.objectcube.com> wrote in message
news:e4AaSxinEHA.648@.tk2msftngp13.phx.gbl...
> I run a shared hosting server where multiple people share a single SQL
> server (each of them have their own data base). I need a way to monitor to
> find out what % of the resources each user is taking as we don't want any
> one person abusing the system. What is the best way to do this
> Jay
>