I know SQL Server is a pretty powerful beast and I can use SQL Server Agent
Jobs to run things on a timed basis, but can anyone answer...
Is it possible to write scripts or stored procedures (or whatever is needed)
to disconnect a user from the database after a period of inactivity?
If so, can someone give me a clue how I would do this or possibly point me
in the right direction.
Thanks!Hi,
Yes it is possible to do, But please check the status of that user before
disconnecting. If it is runnable please wait for some more time and
kill the user once the status become "Sleeping"
declare @.status varchar(30)
declare @.pid int,@.sql nvarchar(100)
select @.status=status from master..sysprocesses where loginame='user_name'
if @.status = 'sleeping'
begin
select @.pid = spid from master..sysprocesses where loginame='user_name'
set @.sql= 'kill '+convert(char,@.pid)
exec sp_executesql @.sql
end
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
MCDBA
"Dave Roys" <daveroys@.xtra.co.nz> wrote in message
news:u4FeDTmAEHA.2600@.TK2MSFTNGP09.phx.gbl...
> I know SQL Server is a pretty powerful beast and I can use SQL Server
Agent
> Jobs to run things on a timed basis, but can anyone answer...
> Is it possible to write scripts or stored procedures (or whatever is
needed)
> to disconnect a user from the database after a period of inactivity?
> If so, can someone give me a clue how I would do this or possibly point me
> in the right direction.
> Thanks!
>
Showing posts with label pretty. Show all posts
Showing posts with label pretty. Show all posts
Sunday, March 25, 2012
Sunday, March 11, 2012
Can DTS read a file as one big blob?
(SQL Server 2000, SP3a)
Hello all!
I've got a pretty simple XML file that I'd like to process with DTS. I unde
rstand that
DTS doesn't have native capability for XML, so I was wondering if I could st
ill use DTS to
read in the entire source file and pass that into a stored procedure as a TE
XT argument?
Thanks for any help you can provide!
John PetersonHi John,
Not sure what you need to accomplish or what exactly process
the file means but with XML files, another option is to use
SQL Server 2000 Web Services Toolkit or just install SQLXML.
You can use the SQL XML Bulk Load object model to import XML
files. You could write this in an ActiveX script task in
DTS.
-Sue
On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a pretty simple XML file that I'd like to process with DTS. I und
erstand that
>DTS doesn't have native capability for XML, so I was wondering if I could s
till use DTS to
>read in the entire source file and pass that into a stored procedure as a T
EXT argument?
>Thanks for any help you can provide!
>John Peterson
>|||Thanks Sue!
Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not w
holly familiar
with SQLXML -- my impression is that it's an add-on to SQL Server, and I kno
w the initial
reaction to our Hosting department will be to understand *why* it's necessar
y for us to
install that on the Production servers. But, if it's the right thing to do,
they'll
oblige.
I had thought to easily leverage some of the built-in XML features of SQL Se
rver, and I
think I could get DTS to invoke a SP that does some manipulation with OPENXM
L(), but I
wasn't sure how to get DTS to potentially squirt the contents of the XML fil
e into a NTEXT
parameter to the SP.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
> Hi John,
> Not sure what you need to accomplish or what exactly process
> the file means but with XML files, another option is to use
> SQL Server 2000 Web Services Toolkit or just install SQLXML.
> You can use the SQL XML Bulk Load object model to import XML
> files. You could write this in an ActiveX script task in
> DTS.
> -Sue
> On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
to
argument?
>|||Yup...the SQLXML bulk load object is part of SQLXML. It's
an add-on or additional component to install. I've used
SQLXML Bulk Load in ActiveX scripts to import XML files into
the relational tables.
The SQLXML OLEDB provider that comes with it also supports
inserting XML data - but I haven't messed with it and don't
know much about it other than it exposes the functionality
through ADO. There is also a managed provider to work with
.Net
The help files that come with it are pretty good and have
examples, samples that can be used to test out
functionality. You may want to download and install it on
your PC to see if it works for you. Excuse the long link and
watch out for line wrap:
http://www.microsoft.com/downloads/...&displaylang=en
-Sue
On Mon, 15 Mar 2004 08:53:49 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks Sue!
>Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not
wholly familiar
>with SQLXML -- my impression is that it's an add-on to SQL Server, and I kn
ow the initial
>reaction to our Hosting department will be to understand *why* it's necessa
ry for us to
>install that on the Production servers. But, if it's the right thing to do
, they'll
>oblige.
>I had thought to easily leverage some of the built-in XML features of SQL S
erver, and I
>think I could get DTS to invoke a SP that does some manipulation with OPENX
ML(), but I
>wasn't sure how to get DTS to potentially squirt the contents of the XML fi
le into a NTEXT
>parameter to the SP.
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
>to
>argument?
>
Hello all!
I've got a pretty simple XML file that I'd like to process with DTS. I unde
rstand that
DTS doesn't have native capability for XML, so I was wondering if I could st
ill use DTS to
read in the entire source file and pass that into a stored procedure as a TE
XT argument?
Thanks for any help you can provide!
John PetersonHi John,
Not sure what you need to accomplish or what exactly process
the file means but with XML files, another option is to use
SQL Server 2000 Web Services Toolkit or just install SQLXML.
You can use the SQL XML Bulk Load object model to import XML
files. You could write this in an ActiveX script task in
DTS.
-Sue
On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I've got a pretty simple XML file that I'd like to process with DTS. I und
erstand that
>DTS doesn't have native capability for XML, so I was wondering if I could s
till use DTS to
>read in the entire source file and pass that into a stored procedure as a T
EXT argument?
>Thanks for any help you can provide!
>John Peterson
>|||Thanks Sue!
Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not w
holly familiar
with SQLXML -- my impression is that it's an add-on to SQL Server, and I kno
w the initial
reaction to our Hosting department will be to understand *why* it's necessar
y for us to
install that on the Production servers. But, if it's the right thing to do,
they'll
oblige.
I had thought to easily leverage some of the built-in XML features of SQL Se
rver, and I
think I could get DTS to invoke a SP that does some manipulation with OPENXM
L(), but I
wasn't sure how to get DTS to potentially squirt the contents of the XML fil
e into a NTEXT
parameter to the SP.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
> Hi John,
> Not sure what you need to accomplish or what exactly process
> the file means but with XML files, another option is to use
> SQL Server 2000 Web Services Toolkit or just install SQLXML.
> You can use the SQL XML Bulk Load object model to import XML
> files. You could write this in an ActiveX script task in
> DTS.
> -Sue
> On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
to
argument?
>|||Yup...the SQLXML bulk load object is part of SQLXML. It's
an add-on or additional component to install. I've used
SQLXML Bulk Load in ActiveX scripts to import XML files into
the relational tables.
The SQLXML OLEDB provider that comes with it also supports
inserting XML data - but I haven't messed with it and don't
know much about it other than it exposes the functionality
through ADO. There is also a managed provider to work with
.Net
The help files that come with it are pretty good and have
examples, samples that can be used to test out
functionality. You may want to download and install it on
your PC to see if it works for you. Excuse the long link and
watch out for line wrap:
http://www.microsoft.com/downloads/...&displaylang=en
-Sue
On Mon, 15 Mar 2004 08:53:49 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks Sue!
>Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not
wholly familiar
>with SQLXML -- my impression is that it's an add-on to SQL Server, and I kn
ow the initial
>reaction to our Hosting department will be to understand *why* it's necessa
ry for us to
>install that on the Production servers. But, if it's the right thing to do
, they'll
>oblige.
>I had thought to easily leverage some of the built-in XML features of SQL S
erver, and I
>think I could get DTS to invoke a SP that does some manipulation with OPENX
ML(), but I
>wasn't sure how to get DTS to potentially squirt the contents of the XML fi
le into a NTEXT
>parameter to the SP.
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
>to
>argument?
>
Sunday, February 12, 2012
Calling webservice from CLR
I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.
Any help would be appreciated
Aaron
You need to
1) creat SQL Server Project and create CRL stored Procedure.
2) add your wsdl file to web reference that points to your web service
3) call web method in your CRL stored Procedure
The following link might be useful to you.
http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx
Calling webservice from CLR
I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.
Any help would be appreciated
Aaron
You need to
1) creat SQL Server Project and create CRL stored Procedure.
2) add your wsdl file to web reference that points to your web service
3) call web method in your CRL stored Procedure
The following link might be useful to you.
http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx
Subscribe to:
Posts (Atom)