Showing posts with label application. Show all posts
Showing posts with label application. Show all posts

Tuesday, March 27, 2012

Calling Stored Procedure from asp without waiting

Hi,
I'm developping a asp application that somestimes needs some heavy
synchronisation done. I wrote a stored procedure for that purpose witch
takes approximatly 15 minutes. Right now I run this directly on the
server (using MS SQL Server management studio). But I would love to be
able to call it from a asp-page. But I don't want the asp script to
wait for 15 minutes before returning. I figured, it should be possible
to tell the stored procedure call that it should run on the server on
its own. Is this possible? And how? Are there better/other solutions?
Thnx for your time,
Anne SchuthIs this done once a day?
Maybe you should create a scheduled job
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thnx for your reply.
No, I should have mentioned that. It's not done with a regular interval
at all.
Anne|||In ASP.Net you could use an asynchronous call.
If you create a job in SQL Server Agent, you could call a stored procedure
that starts the job. The stored procedure does not wait for the job to
finish, so neither does your ASP page.
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||is it asp.NET - in which case you want to use an asynchronous thread to
fire off the sp|||I would build an ASP page.. which called a stored procedure.
And that stored procedure would schedule a job (like 10 seconds into the
future) to run.. and that job would run a stored procedure which did your
15min of work.
uspFireOffTheJobWhichRunsTheMainStoredPr
ocedure
-- the contents of this stor proc would be
dbo.uspJobScheduleUpdate ( 'MyLongJob1' , 'JobSchedule1ForMyLongJob1' , 20
uspLongRunningStoredProcedure
--the contents of this proc would be ... your code to do the 15 minutes or
work.
..
That was about 2 ws of my time (the stuff above and below), so post a
thank you... if you go this route.
Here is some code to help ,,, this would programmatically CREATE the job.
The 2 procedure above ... esp the uspFireOffxxxxx would be needed to
SCHEDULE the job
declare @.jobName varchar(128)
declare @.jobStepName varchar(128)
declare @.jobScheduleName varchar(128)
declare @.uspName varchar(128)
declare @.uuid uniqueidentifier
select @.jobName = 'MyLongJob1'
select @.uspName = 'dbo.uspLongRunningStoredProcedure'
select @.jobStepName = 'JobStep1For' + @.jobName
select @.jobScheduleName = 'JobSchedule1For' + @.jobName
select @.uspName = 'EXEC ' + @.uspName
declare @.serverName varchar(128)
select @.serverName = CONVERT(varchar(128) , SERVERPROPERTY('servername') )
--print @.serverName
declare @.dbName varchar(128)
select @.dbName = DB_NAME()
EXEC msdb.dbo.sp_add_job @.job_name = @.jobName,
@.enabled = 1,
@.description = @.jobName,
@.owner_login_name = 'sa' -- << THIS ONE YOU NEED TO WATCH .. and use
the loginname that your asp page uses to connect to the db()
EXEC msdb.dbo.sp_add_jobstep @.job_name = @.jobName,
@.step_name = @.jobStepName,
@.subsystem = 'TSQL',
@.database_name = @.dbName ,
@.command = @.uspName
EXEC msdb.dbo.sp_add_jobserver @.job_name = @.jobName,
@.server_name = @.serverName
-- END create jobs programatically
here is a final script.. I created to update the schedule on a job .. by
using its name, schedueName and delay seconds.
in my testing I found 10 seconds was the smallest increment I could use.
if exists (select * from sysobjects
where id = object_id('dbo.uspJobScheduleUpdate') and sysstat & 0xf = 4)
drop procedure dbo.uspJobScheduleUpdate
GO
CREATE Procedure dbo.uspJobScheduleUpdate ( @.jobName varchar(128) ,
@.jobScheduleName varchar(128) , @.delaySeconds int )
AS
SET NOCOUNT ON
declare @.minimumTimeDelaySeconds int
Select @.minimumTimeDelaySeconds = 10
if @.delaySeconds < @.minimumTimeDelaySeconds
BEGIN
select @.delaySeconds = @.minimumTimeDelaySeconds
END
declare @.now datetime
select @.now = GETDATE()
Select @.now = DATEADD(s , @.delaySeconds , @.now)
--print @.now
--select DATEPART(s , @.now)
--select DATEPART(hh , @.now)
--select DATEPART(mi , @.now)
/*
[@.active_start_date =] active_start_date
Is the date on which execution of the job can begin. active_start_date is
int,
with a default of NULL. Values must be formatted as YYYYMMDD.
If active_start_date is not NULL, the date must be greater than or equal to
19900101.
*/
declare @.year varchar(4)
declare @.month varchar(2)
declare @.day varchar(2)
declare @.yearint int
declare @.monthint int
declare @.dayint int
declare @.hour varchar(2)
declare @.minute varchar(2)
declare @.second varchar(2)
declare @.hourint int
declare @.minuteint int
declare @.secondint int
--year does not need prefixed "0"
select @.yearint= DATEPART(yyyy , @.now)
select @.year = convert(varchar(4) , @.yearint )
select @.monthint= DATEPART(m , @.now)
if @.monthint < 10
BEGIN
select @.month = '0' + convert(varchar(1) , @.monthint )
END
else
BEGIN
select @.month = convert(varchar(2) , @.monthint )
END
select @.dayint= DATEPART(d , @.now)
if @.dayint < 10
BEGIN
select @.day = '0' + convert(varchar(1) , @.dayint )
END
else
BEGIN
select @.day = convert(varchar(2) , @.dayint )
END

select @.hourint= DATEPART(hh , @.now)
if @.hourint < 10
BEGIN
select @.hour = '0' + convert(varchar(1) , @.hourint )
END
else
BEGIN
select @.hour = convert(varchar(2) , @.hourint )
END
select @.minuteint= DATEPART(mi , @.now)
if @.minuteint < 10
BEGIN
select @.minute = '0' + convert(varchar(1) , @.minuteint )
END
else
BEGIN
select @.minute = convert(varchar(2) , @.minuteint )
END
select @.secondint= DATEPART(s , @.now)
if @.secondint < 10
BEGIN
select @.second = '0' + convert(varchar(1) , @.secondint )
END
else
BEGIN
select @.second = convert(varchar(2) , @.secondint )
END
--print '*' + @.hour + '*'
--print '*' + @.minute + '*'
--print '*' + @.second + '*'
declare @.derivedstartdate varchar(12)
select @.derivedstartdate = @.year + @.month + @.day
declare @.derivedstarttime varchar(12)
select @.derivedstarttime = @.hour + @.minute + @.second
--print @.derivedstartdate
--print @.derivedstarttime
declare @.alreadyExists bit
declare @.enabled bit
declare @.active_start_date int
declare @.active_start_time int
EXEC dbo.uspJobScheduleExists @.jobName , @.jobScheduleName , @.alreadyExists
output , @.enabled output , @.active_start_date output , @.active_start_time
output
/*
print 'Debugging Values'
print '@.jobName=' + @.jobName
print '@.jobScheduleName=' + @.jobScheduleName
print '@.alreadyExists=' + convert(varchar(8) , @.alreadyExists)
print '@.enabled=' + convert(varchar(8) , @.enabled)
print '@.active_start_date=' + convert(varchar(16) , @.active_start_date)
print '@.active_start_time=' + convert(varchar(16) , @.active_start_time)
print '/Debugging Values'
*/
declare @.jobSchedulingCheck int
if @.alreadyExists = 0 --FALSE
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_add_jobschedule
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
else
BEGIN
declare @.allClearToUpdateTheJob bit
select @.allClearToUpdateTheJob = 1 -- default to true
if @.enabled <> 0 -- The job is enabled,,but how far in the future'
BEGIN
--most times, if its enabled..then its set to fire pretty quickly in the
future..so set it to false..but also check a "too far in the future" date
select @.allClearToUpdateTheJob = 0 -- false
--/*
if @.active_start_date - @.derivedstartdate >= 0 -- the date is in the future
..or today
begin
if @.active_start_time - @.derivedstarttime > @.delaySeconds -- the time is
greater in the future than the delay period
begin
--print 'too far into the future for the job!'
select @.allClearToUpdateTheJob = 1-- while it was enabled..it was too far
into the future
end
end
--*/
END
--if its not enabled, then the @.allClearToUpdateTheJob value will not
changed..thus it will be updated
if @.allClearToUpdateTheJob <> 0
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_update_jobschedule --0 (success) or 1
(failure)
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
END
--Return Code Values
--0 (success) or 1 (failure)
if (@.jobSchedulingCheck<>0)
BEGIN
INSERT INTO dbo.tblAuditTrail (SubjectID , ObjectID , ShortDescription)
VALUES (@.jobName , @.jobScheduleName, 'sp_add_jobschedule or
sp_update_jobschedule FAILED.')
END
SELECT( @.jobSchedulingCheck )
if (@.jobSchedulingCheck IS NOT NULL)
begin
return @.jobSchedulingCheck
end
SET NOCOUNT OFF
GO
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145633124.959348.250700@.g10g2000cwb.googlegroups.com...
> Thnx for your reply.
> No, I should have mentioned that. It's not done with a regular interval
> at all.
> Anne
>|||Yeah, that does deserve a THANK YOU!
I will need some time to get it all together and running but this
certainly points me in the right direction! I've never used jobs
before, so din't think of it.
Thnx all for the quick replies!
(NP: I don't use asp.net)|||wow'
all that hour minute second stuff can be replace with
declare @.now datetime
select @.now = getdate()
select
convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||If you are on 2005, consider using Service Broker for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||
Thanks,
I felt like what I was doing was too much drama when I wrote it.
I appreciate the convert tip.
SQL wrote:
> wow'
> all that hour minute second stuff can be replace with
> declare @.now datetime
> select @.now = getdate()
> select
> convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/

Calling stored procedure from a C application

I have an ODBC/C program and I want to call the stored procedures
sp_addlogin and sp_adduser in it. Can someone please provide me with
some sample code showing the best way to do this.[posted and mailed, please reply in news]

Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> I have an ODBC/C program and I want to call the stored procedures
> sp_addlogin and sp_adduser in it. Can someone please provide me with
> some sample code showing the best way to do this.

http://www.microsoft.com/downloads/...&DisplayLang=en

These samples were also in the original Books Online that came with SQL
Server, but was removed from later versions. The above link gives you
revised samples. I believe the main difference is that the revised samples
uses Windows authentication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955E841276F14Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> > I have an ODBC/C program and I want to call the stored procedures
> > sp_addlogin and sp_adduser in it. Can someone please provide me with
> > some sample code showing the best way to do this.
> http://www.microsoft.com/downloads/...&DisplayLang=en
> These samples were also in the original Books Online that came with SQL
> Server, but was removed from later versions. The above link gives you
> revised samples. I believe the main difference is that the revised samples
> uses Windows authentication.

Hi,

Did you try using EXEC sp_addlogin from C program.

Sunday, March 25, 2012

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
Hao
It is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPendpointTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>
|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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 Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone there
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent
|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.co m...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>
|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/WindowsServer/en/library/b3a029a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
|||Hello Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.

Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
HaoIt is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPe...tTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone ther
e
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.com...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/Windo...a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows200...isting/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||Hello Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.sql

Calling SQL editor dialog from application

The SQL (or query) editor in Visual Studio is a great tool. Is there a way to call the editor from my application to edit a SQL string?

This is not for the end user but as a developer tool for those who have a copy of Visual Studio. I would like to be able to visually design the SQL commands we are storing in a database.

Thanks, Dave C.

Hi Dave,
There is no direct way to launch the SQL Query editor based on a string in your application. The best you can do is save the string to a file and then use the command line parameters for loading that file into SSMS using SQLWB.EXE.
Thank you,
Bill Ramos, PM, SSMS

calling report in asp.net app

Hello,
I created a few reports by using RS2000, now I want to call these reports in
my asp.net web application through a selection box. What is the best and
easiest way to handle this.
Thanks,
Jim.if you want a dynamic list (a list constructs from the reports available) so
you can access RS through its webservice and retrieve the list of available
reports
Or if the list is limited , then you can fill manually the selection box
with "static" values.
To open and render the report, if the ASP.NET application is on the same
server as RS, then simply open the report with URL access:
/reportserver?/folder/report name
You can use the sample ASP.net control provided with RS to disply the
report.
"JIM.H." <JIMH@.discussions.microsoft.com> a écrit dans le message de news:
185467F1-14DD-4658-A5EE-A2024FC6290D@.microsoft.com...
> Hello,
> I created a few reports by using RS2000, now I want to call these reports
> in
> my asp.net web application through a selection box. What is the best and
> easiest way to handle this.
> Thanks,
> Jim.
>|||Jim,
Easiest may not always be the best way since it depends on your report
requirements. But in general, you should gravitate whenever possible to URL
addressability. The ReportViewer sample that comes with RS makes this
easier.
If you want to be able to switch between URL and SOAP addressibility you may
find my AwReportViewer control (an enhanced version of ReportViewer useful
(http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45). Enjoy
it!
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:185467F1-14DD-4658-A5EE-A2024FC6290D@.microsoft.com...
> Hello,
> I created a few reports by using RS2000, now I want to call these reports
in
> my asp.net web application through a selection box. What is the best and
> easiest way to handle this.
> Thanks,
> Jim.
>

Thursday, March 22, 2012

Calling one application from SQL Server

Is it possble to automatically call an application when another application makes an INSERT from inside sql server? If its possible - how do I do that?

Thx in advance

/MiZzaYou can get SQL to make COM style calls...look up extended procedures. However, you'd still need a way to identify what the calling application was.|||Main stored procedures to use:

sp_OACreate - Creates an instance of the OLE object on an instance of Microsoft® SQL Server?. (e.g. COM component)

sp_OAMethod
sp_OADestroy

Support stored procedures to use:

sp_OAGetErrorInfo
sp_OAGetProperty
sp_OASetProperty
sp_OAStop

Calling Java Application from PL/SQL code

Colleagues,

I am not an expert in PL/SQL neither Java, so I require a help here:

What I am planning to do is to use PL/SQL to call a Java application (pass 3 arguments).

The Java application is used to make call to an API that resides on a Bea Weblogic application/web server (its not used to access database).

I got already the PL/SQL Trigger, but I need to add to it the call to the Java application (the Java application already exists)

Simply, would any one provide an example of how to make that call.

Do I need to load Java classes into the Oracle database or not?

I am using Oracle 9i database, and the Java application is compiled using Java SDK 1.4.2

Many thanksHi,

To call java class from a pl/sql, you need to follow the steps.

1. First create a java file and compile it.

2. Load the java class file to the database using 'loadjava' utility.

Ex: loadjava -u scott/tiger abc.class

3. Create a pl/sql wrapper aroung the java class like

CREATE OR REPLACE PROCEDURE test (a1 VARCHAR2) AS LANGUAGE JAVA name 'abc.main(java.lang.String[])';

4. Then execute the stored procedure

exec test(<input>);|||HI,

Invoking the java stored procedures from any PL/SQL block or stored module works the same way it would for any other PL/SQL stored function.

For example:

SQLPLUS> set serveroutput on size 5000;

SQLPLUS> declare
2> temp varchar2(100);
3> begin
4> temp := ConcatTwo('Hello','World');
5> DBMS_OUTPUT.PUT_LINE(temp);
6> end;
7> /|||Thanks... suneel.kumar and satish_ct

you realy give me some ideas...|||These examples provide the capability to load specific "classes" to the
database and most will work however Oracle will have problems with
classes that import from GUI packages such as AWT or Swing.|||HI,

Did U come across any such error?|||My class is only initiate a request or call to another system API (my class doesn't import from any GUI Packages).

Many thanks to you all|||when I've tried to load the java class, but I got the following error:

ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","joxs heap init","ioc_allocate_pal")

would some one told me what I should do.

I think that the shared pool is out of memory so should I consult the DBA, system Admin or is it from my PC, where I am making a conection to the server/Database.

Tuesday, March 20, 2012

Calling External Assemblies which require there own condig setting

Hi,
I've successully called a method of an external assembly (which is part of a
large web-based application) from within the report designer.
However, when I call a different method from the same external assembly I
get an error "Failed to load expression host assembly. Details : The
ConnectionString property has not been initialized".
Now I know the Details part of the error is coming from the external
assembly. Looking at it's code it's calling another assembly and attempting
to set a connection string so that it can access it's own database. The value
of the connection string usually comes from a Web.Config file.
My question is how does .NET decide which config file to search in this
case? I've tried placing the connection string part in machineconfig and
RSReportDesigner.config.
Haven't done code access securiy yet as, according to Teo's book Reporting
Services in Action, when you preview a report it assigns it full trust.
Cheers.
MartinHi,
A colleague of mine pointed out that the entries I made to machine.config
were commented out. Once uncommented it now works!
Still, would be nice to know what's happening re .NET searching for config
files.
Cheers,
Martin

Calling applications in unix platform

Dear all,

How do i call an application, which is reside in another unix platform in my flow of execution.

I'll need to call this application after my data extraction is successfully run.

Thanks for the helps in advance.

Thanks & Best Regards,

PassionOrienter

Hi PassionOrienter,

The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.

Hope this helps!

-David

|||

Hi David,

Thanks for the solution provided.

I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.

Anyway, thanks for the help.

Thanks & Best Regards,

PassionOrienter

Calling applications in unix platform

Dear all,

How do i call an application, which is reside in another unix platform in my flow of execution.

I'll need to call this application after my data extraction is successfully run.

Thanks for the helps in advance.

Thanks & Best Regards,

PassionOrienter

Hi PassionOrienter,

The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.

Hope this helps!

-David

|||

Hi David,

Thanks for the solution provided.

I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.

Anyway, thanks for the help.

Thanks & Best Regards,

PassionOrienter

Calling applications in unix platform

Dear all,

How do i call an application, which is reside in another unix platform in my flow of execution.

I'll need to call this application after my data extraction is successfully run.

Thanks for the helps in advance.

Thanks & Best Regards,

PassionOrienter

Hi PassionOrienter,

The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.

Hope this helps!

-David

|||

Hi David,

Thanks for the solution provided.

I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.

Anyway, thanks for the help.

Thanks & Best Regards,

PassionOrienter

Calling another .sql file from an existing .sql file

I have a vb application that executes a .sql file called "fileA.sql" using
the osql cmd.
I would like to know how to make fileA.sql call fileB.sql.
In an oracle based .sql file, I generally would call fileB.sql from within
fileA.sql using
the following script
@.@.fileB.sql
This does not seem to work against sql server.
Thanks.
--
ANeelimaHi
File calls another file? I'm not aware of such issue?
However , you can call the file to do some operations as
UPDATE/INSERT/DELETE/SELECT and then make some logic here.
D you mean that fileA does not contain any info about fileB?
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||Yes, the intention is for FileA.sql to call FileB.sql. Instead of calling
all the files individually from my VB application I wanted to call one singl
e
file called FileA.sql
and have FileA.sql call FileB.sql, FileC.sql, and so on.
I can do that when executing sql files against Oracle without any problems.
So, if it were Oracle, the script in FileA.sql will be
----
set verify off;
set echo off;
@.@.FileB.ql;
PAUSE;
@.@.FileC.sql;
EXIT;
----
If I use the same script against SQL Server I get the error saying that
@.@.FileB.sql is not recognized.
ANeelima
"Uri Dimant" wrote:

> Hi
> File calls another file? I'm not aware of such issue?
> However , you can call the file to do some operations as
> UPDATE/INSERT/DELETE/SELECT and then make some logic here.
> D you mean that fileA does not contain any info about fileB?
>
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>
>|||In file A, use OSQL (again) to call file B.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
...
To my knowledge there isn't a "built in" preprocessor as you describe.
Generally people use stored procedures instead of file level modules. I
have heard of people using a C language preprocessor with regard to
configuration management with few difficulties.
B|||In order to use osql to call file B from file A, I would have to hard-code
the database and server name in File A as in
osql -E -n -S %COMPUTERNAME%\MYINSTANCE -dENDESKTOP11 -i "FileB.sql" -o
"FileB.log"
But, my goal is to not hard-code the database nor the instance/server name
in FileA.sql since the user chooses the database and server name at run-time
from the VB application.
The VB application then uses the selected names and executes a osql command
and runs FileA.sql using the shell cmd programmatically. Thus I was hoping
that there was a way to simply set the database and server in FileA.sql
(which is the parent sql file) and have FileA call FileB, FileC, etc.
Based on the responses, it appears like I cannot do that as in Oracle.
--
ANeelima
"Arnie Rowland" wrote:

> In file A, use OSQL (again) to call file B.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>
>|||Hi, ANeelima,
Thanks for your post.
From your description, I understand that:
Your VB application used osql command to execute SQL script files. In your
case, you wanted one sql file can invoke another sql file avoiding hard
code of osql in it.
If I have misunderstood, please let me know.
I don't find other commands or functions can invoke other sql files without
login, which may be different from Oracle.
However, in my experiences, such expression @.@.fileb.sql is supported by
SQL*Plus, while it is not supported by OSQL.
You can only pass the sql file name as -i parameter of osql command.
If you want to just connect your database server only once, I recommend you
use ADO or ODBC to connect the server and the read and execute the sql
files line by line.
Also, if you don't care about the connection times of osql, you may create
a .bat file with osql command and the execute the .bat file only once.
Also, for such feature request, you may click the link below and give
Microsoft feedback for product improvement. Your suggestions are most
valuable to Microsoft.
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
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.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang[MSFT]
Microsoft Developer Online Support

Calling another .sql file from an existing .sql file

I have a vb application that executes a .sql file called "fileA.sql" using
the osql cmd.
I would like to know how to make fileA.sql call fileB.sql.
In an oracle based .sql file, I generally would call fileB.sql from within
fileA.sql using
the following script
@.@.fileB.sql
This does not seem to work against sql server.
Thanks.
--
ANeelimaHi
File calls another file? I'm not aware of such issue?
However , you can call the file to do some operations as
UPDATE/INSERT/DELETE/SELECT and then make some logic here.
D you mean that fileA does not contain any info about fileB?
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||Yes, the intention is for FileA.sql to call FileB.sql. Instead of calling
all the files individually from my VB application I wanted to call one single
file called FileA.sql
and have FileA.sql call FileB.sql, FileC.sql, and so on.
I can do that when executing sql files against Oracle without any problems.
So, if it were Oracle, the script in FileA.sql will be
----
set verify off;
set echo off;
@.@.FileB.ql;
PAUSE;
@.@.FileC.sql;
EXIT;
----
If I use the same script against SQL Server I get the error saying that
@.@.FileB.sql is not recognized.
ANeelima
"Uri Dimant" wrote:
> Hi
> File calls another file? I'm not aware of such issue?
> However , you can call the file to do some operations as
> UPDATE/INSERT/DELETE/SELECT and then make some logic here.
> D you mean that fileA does not contain any info about fileB?
>
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
> >I have a vb application that executes a .sql file called "fileA.sql" using
> > the osql cmd.
> > I would like to know how to make fileA.sql call fileB.sql.
> >
> > In an oracle based .sql file, I generally would call fileB.sql from within
> > fileA.sql using
> > the following script
> >
> > @.@.fileB.sql
> >
> > This does not seem to work against sql server.
> >
> > Thanks.
> > --
> > ANeelima
>
>|||In file A, use OSQL (again) to call file B.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
> Thanks.
> --
> ANeelima|||"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
>I have a vb application that executes a .sql file called "fileA.sql" using
> the osql cmd.
> I would like to know how to make fileA.sql call fileB.sql.
> In an oracle based .sql file, I generally would call fileB.sql from within
> fileA.sql using
> the following script
> @.@.fileB.sql
> This does not seem to work against sql server.
...
To my knowledge there isn't a "built in" preprocessor as you describe.
Generally people use stored procedures instead of file level modules. I
have heard of people using a C language preprocessor with regard to
configuration management with few difficulties.
B|||In order to use osql to call file B from file A, I would have to hard-code
the database and server name in File A as in
osql -E -n -S %COMPUTERNAME%\MYINSTANCE -dENDESKTOP11 -i "FileB.sql" -o
"FileB.log"
But, my goal is to not hard-code the database nor the instance/server name
in FileA.sql since the user chooses the database and server name at run-time
from the VB application.
The VB application then uses the selected names and executes a osql command
and runs FileA.sql using the shell cmd programmatically. Thus I was hoping
that there was a way to simply set the database and server in FileA.sql
(which is the parent sql file) and have FileA call FileB, FileC, etc.
Based on the responses, it appears like I cannot do that as in Oracle.
--
ANeelima
"Arnie Rowland" wrote:
> In file A, use OSQL (again) to call file B.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:E96C2EE7-7383-47F7-A7B8-801FBE9541E6@.microsoft.com...
> >I have a vb application that executes a .sql file called "fileA.sql" using
> > the osql cmd.
> > I would like to know how to make fileA.sql call fileB.sql.
> >
> > In an oracle based .sql file, I generally would call fileB.sql from within
> > fileA.sql using
> > the following script
> >
> > @.@.fileB.sql
> >
> > This does not seem to work against sql server.
> >
> > Thanks.
> > --
> > ANeelima
>
>|||Hi, ANeelima,
Thanks for your post.
From your description, I understand that:
Your VB application used osql command to execute SQL script files. In your
case, you wanted one sql file can invoke another sql file avoiding hard
code of osql in it.
If I have misunderstood, please let me know.
I don't find other commands or functions can invoke other sql files without
login, which may be different from Oracle.
However, in my experiences, such expression @.@.fileb.sql is supported by
SQL*Plus, while it is not supported by OSQL.
You can only pass the sql file name as -i parameter of osql command.
If you want to just connect your database server only once, I recommend you
use ADO or ODBC to connect the server and the read and execute the sql
files line by line.
Also, if you don't care about the connection times of osql, you may create
a .bat file with osql command and the execute the .bat file only once.
Also, for such feature request, you may click the link below and give
Microsoft feedback for product improvement. Your suggestions are most
valuable to Microsoft.
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
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.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang[MSFT]
Microsoft Developer Online Support

Calling an executable file from stored procedure

Hi people.

I have this issue

I'm on a application server and i need to run a executable file from a SP, my problem is if i use xp_cmdshell (from the SP) it will try to run it on the SQL Server not on a App server, this a third party executable so cannnot be run on the sql backend.

Do you know any workaround for this?have the client launch it on it's own.

what business does the server have launching apps on the client anyway?|||jez,

you never call a report engine or a command line DOS thingie from you application code?|||Hi people.

I have this issue

I'm on a application server and i need to run a executable file from a SP, my problem is if i use xp_cmdshell (from the SP) it will try to run it on the SQL Server not on a App server, this a third party executable so cannnot be run on the sql backend.

Do you know any workaround for this?

Is this executable a GUI? If so, don't do it...it could hose everything

If it's just like a bat file, or a batch process then you should be fine|||Actually is a exe file with some parameters (not GUI)

Picture this

Server A
Application Server

Server B
SQL Server 2005

on Server A, i'm executing a SP wich is located on Server B if i use xp_cmdshell is going to look on server B but the exe file is located on server A... can i do like a Remote Execution?|||Why wouldn't you place the executable on both servers?|||won't work the executable on the SQL Server by itself, i need to install the whole application and we don't have the license for it.|||Can't your xp_cmdshell just include the complete path to the executable?

as in xp_cmdshell '\\MyRemoteServer\MysteryJob\DoSomething.bat' works fine where I live.

Worst case you could have your bat file start the exe. We do some of that, though it's too close to 5:30 on Friday for me to make a decent effort at finding the applicable code at this time.|||jez,

you never call a report engine or a command line DOS thingie from you application code?

sure, but that's not how I read the question.

how I read it was, "how do I launch an exe that lives on the client from a stored procedure on the server?"

perhaps I misinterpreted.

Calling an application from SQL (xp_cmdshell)

Hi,

I am trying to run an executable from MS SQL Server 2000. I tried using the commands:

EXEC master..xp_cmdshell '"\\comp1\c\test.exe"', NO_OUTPUT

and

EXEC master..xp_cmdshell 'Start Test "\\comp1\c\test.exe" , NO_OUTPUT

When I go to task manager on comp1 I see that the process is listed as running only under Processes, not under Application. In addition the application doesnt seem to execute properly and the SQL Query doesnt terminate. For testing processes I have just a small VB project that pops up a message box and then exits (it works fine by running it from windows). I never see the message box, nor does it exit the process.

If I try to manually cancel the SQL Query, it still will not stop until I kill the process for test.exe from the task manager.

Does anyone have any ideas on how I could get the query to terminate properly and how to get the executable to do its thing?

Sincerely,
Andrei Girenkovyeah...don't call gui apps from xp_cmdshell unless you want to hang your server...

Only use batch executables that don't require ANY prompting...|||Well I wasn't aware of that, however the executable I'm trying to launch actually doesnt have any sort of GUI...all it does is checks some output from another program and updates some DB tables.

I found that after launching the executable through double clicking it worked fine, but when launching it from SQL it didn't update the DB. I made the small project with the message box as an easier way of testing it rather than having to go to SQL every time and checking whether or not a table got updated.

~Andrei|||Can you be more specific as to what this executable is doing ?|||[i]I made the small project with the message box
~Andrei

You don't think a message box is a gui?|||I mean the actual executable I am trying to run has no GUI. The messagebox program is only for testing. I now scrapped the message box project because of the GUI and am trying to just run/test with the actual executable, however I am not seeing any results either.

My program does the following: I am trying to record the Volume Serial of the C drive of the machine the instance of the SQL server is running. I have an executable which reads the volume serial, scrambles it, records it into the DB, and then exits out. It takes as a command line argument the name of the instance of SQL server on that machine.

I am trying to write a stored procedure that I could call which would run this executable and pass it the instance name as an argument. The call in the stored procedure is the following:

EXEC master..xp_cmdshell '"C:\volumeserial.exe" -' + (select @.@.SERVERNAME), NO_OUTPUT

Right now the select @.@.SERVERNAME part is hardcoded to the server name because I was getting an syntax error at the + before the select statement, and the comma after it. It actually looks like this:

EXEC master..xp_cmdshell '"C:\volumeserial.exe" -InstanceName', NO_OUTPUT

When I call this stored procedure I can see in the task manager under processes that Volumeserial.exe is running, however the DB doesnt get updated, the process never terminates, and the stored procedure never gets past that line. Like I mentioned earlier, the program works fine if I call it from outside SQL, so I think it's a SQL issue.

~Andrei Girenkov|||One thing to double check would be the connection string to the database. If you are using a trusted connection, remember that the application will be running under the identity of the SQL Server Service account.

Another thing to check would be permissions on the executable. Again, be sure that the SQL Server Service account as execute permissions on the application executable.

Make sure that you test you application executable while logged in as the SQL Server service account.

Regards,

hmscott|||My SQL Server Service account is the Administrator. I can run the executable just fine when logged in as Administrator.

~Andrei|||how about making the executable just return the scrambled serial number without trying to connect to the server and do the update? you can do those things in your stored procedure.

create table #tmp ([output] nvarchar(4000) null)
insert #tmp EXEC master..xp_cmdshell 'C:\volumeserial.exe'
delete #tmp where [output] is null
update y set scrambled_serial = [output]
from your_table y, #tmp where y.drive_letter = 'C'|||Thank you everyone for all your help. I had a hunch that it was not connecting to SQL properly so instead of writing the value back to the DB I had the program write it out to a file along with the command line arguments. It turned out that the problem was caused because in SQL I was passing the InstanceName parameter to the program with a dash (-). The program wasn't expecting that.

The other problem of not being able to select @.@.ServerName was fixed by just declaring the whole string as a varchar() and passing that variable to xp_cmdshell instead of the concatinated string.

Thanks again everybody who helped

~Andreisql

Monday, March 19, 2012

Calling a VB.NET web application from report manager

Does anybody know if I can call a VB.NET web application from within RS
report manager. All my security is contained in the application and all I
want to do is call it from within report manager for centralization reasons.
I was able to create a shortcut and upload that to report manager and it
works up until I try and run the report(s). The error message I get is
"Cannot find /Main.aspx". The VB.NET application itself displays a screen
where the user selects start and end date, division(s) (multiselect) and the
expense report(s) (multiselect) they want to run. They then simply click
"Generate Report" and the application builds the reports programmatically
and places them onto a network share in Excel format. All works well when I
run in IE.
Any help is greatly appreciated.Hello,
Based on my scope, I think because report manager itself is a Web
application, you cannot call another Web applicaiton from within report
manager. You could only upload report to report manager, not a Web
applicaition link.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
| From: "ddillman@.tmsteam.com" <TMSMAN@.community.nospam>
| Subject: Calling a VB.NET web application from report manager
| Date: Fri, 13 May 2005 10:34:00 -0400
| Lines: 15
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <O$4yPj8VFHA.1044@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 216-164-203-3.c5-0.atw-ubr1.atw.pa.cable.rcn.com
216.164.203.3
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43751
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Does anybody know if I can call a VB.NET web application from within RS
| report manager. All my security is contained in the application and all I
| want to do is call it from within report manager for centralization
reasons.
| I was able to create a shortcut and upload that to report manager and it
| works up until I try and run the report(s). The error message I get is
| "Cannot find /Main.aspx". The VB.NET application itself displays a screen
| where the user selects start and end date, division(s) (multiselect) and
the
| expense report(s) (multiselect) they want to run. They then simply click
| "Generate Report" and the application builds the reports programmatically
| and places them onto a network share in Excel format. All works well when
I
| run in IE.
|
| Any help is greatly appreciated.
|
|
|

Thursday, March 8, 2012

Calling a .Net application via a COM wrapper in SQL Server 2005

I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
the option COM Interop set to True. Once built, the VS2003 project solution
produces a .tlb file. A setup is produced (from within VS2003) that includes
the dll and tlb files. These are installed on a PC (running on Windows
2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (with
the latest SQL Server 2000 Service Pack). A trigger fired from one of the
tables in our own database (within SQL Server 2000) makes the calls to the
COM wrapped assembly.
CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
FOR INSERT, UPDATE,DELETE
AS
DECLARE @.hr int
DECLARE @.factory int
DECLARE @.xalert int
DECLARE @.response int
DECLARE @.errMsg varchar(8000)
DECLARE @.success bit
EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUTPUT
select @.hr
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
@.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactory',
'http://localhost:8080/axis/services/xalertsport'
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
'MyXalerts55_030506_ss', 1100
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.xalert , @.hr
RETURN
END
EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
IF (@.success = 0)
BEGIN
EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
END
EXEC @.hr = sp_OADestroy @.xalert
EXEC @.hr = sp_OADestroy @.response
EXEC @.hr = sp_OADestroy @.factory
This works fine and we are able to create the objects and make the method
calls on it.
We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
VS2005, using the same settings as before. A new setup was created and
installed on a PC running on Windows 2000 Server and has SQL Server 2005 SP1
installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
database and our own databse have been made "trustworthy". The assembly
created above (with .net 2,.0) has been created within SQL Server, setting
External_acesss (tried unsafe as well) permissions on it.
We are using the same trigger as above and the same code within the trigger.
The trigger fails on the sp_oacreate line with the following error.
OLE Automation Error
HRESULT: 0x80131700
Source: ODSOLE EXtended Procedure
We have tried to put more COM Wrapper around the generated tlb , but that
has not worked - giving the same error. Using .net 1.1 assembly (same one as
used for SQLServer 2000) , we get the same error. However, It is possible to
create other standard COM objects (like the SQLDMO objects), using the
sp_oacreate. So, it may be something particular to .net assemblies being
called (via the COM interop wrapper) from SQL Server 2005.You should really go another route. Having SQL Server execute code hosted by CLR was never supported
though spOACreate nor xp_cmdshell. I'd consider re-writing that com object as a CLR stored procedure
and you are in a supported environment. Below is my general "canned" response:
It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR;
hosted within SQL Server's adress space..
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
Also, below is witnh permission from David Browne, explaining how you can have SQL Server execute
CLR code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last week, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:EC73FFA6-3F3E-47EE-809E-18BF1D60A88E@.microsoft.com...
>I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
> the option COM Interop set to True. Once built, the VS2003 project solution
> produces a .tlb file. A setup is produced (from within VS2003) that includes
> the dll and tlb files. These are installed on a PC (running on Windows
> 2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (with
> the latest SQL Server 2000 Service Pack). A trigger fired from one of the
> tables in our own database (within SQL Server 2000) makes the calls to the
> COM wrapped assembly.
> CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
> FOR INSERT, UPDATE,DELETE
> AS
> DECLARE @.hr int
> DECLARE @.factory int
> DECLARE @.xalert int
> DECLARE @.response int
> DECLARE @.errMsg varchar(8000)
> DECLARE @.success bit
> EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUTPUT
> select @.hr
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
> @.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactory',
> 'http://localhost:8080/axis/services/xalertsport'
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
> 'MyXalerts55_030506_ss', 1100
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.xalert , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> IF (@.success = 0)
> BEGIN
> EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> END
> EXEC @.hr = sp_OADestroy @.xalert
> EXEC @.hr = sp_OADestroy @.response
> EXEC @.hr = sp_OADestroy @.factory
>
> This works fine and we are able to create the objects and make the method
> calls on it.
> We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
> VS2005, using the same settings as before. A new setup was created and
> installed on a PC running on Windows 2000 Server and has SQL Server 2005 SP1
> installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
> database and our own databse have been made "trustworthy". The assembly
> created above (with .net 2,.0) has been created within SQL Server, setting
> External_acesss (tried unsafe as well) permissions on it.
> We are using the same trigger as above and the same code within the trigger.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
>
> We have tried to put more COM Wrapper around the generated tlb , but that
> has not worked - giving the same error. Using .net 1.1 assembly (same one as
> used for SQLServer 2000) , we get the same error. However, It is possible to
> create other standard COM objects (like the SQLDMO objects), using the
> sp_oacreate. So, it may be something particular to .net assemblies being
> called (via the COM interop wrapper) from SQL Server 2005.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> You should really go another route. Having SQL Server execute code hosted
> by CLR was never supported though spOACreate nor xp_cmdshell. I'd consider
> re-writing that com object as a CLR stored procedure and you are in a
> supported environment. Below is my general "canned" response:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's adress space..
> See:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
> Also, below is witnh permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
>
Thanks,
But if I understand the question, the OP has compiled and installed the
assembly in SQL CLR. If so, he just needs to issue write a CLR stored
procedure in .NET that implements the trigger logic. Accessing the assembly
the OLE Automation stored procedures is no longer necessary. The trigger
body just needs to invoke a CLR stored procedure.
David|||Yes, I fully agree. However, the first post indicates that this isn't what the OP had in mind. Or
perhaps the first post seemed a bit ambiguous on this:
> We are using the same trigger as above and the same code within the trigger.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
Thanks for making that option explicit, though. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:%23Hf3w3dmGHA.4536@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
>> You should really go another route. Having SQL Server execute code hosted by CLR was never
>> supported though spOACreate nor xp_cmdshell. I'd consider re-writing that com object as a CLR
>> stored procedure and you are in a supported environment. Below is my general "canned" response:
>> It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR;
>> hosted within SQL Server's adress space..
>> See:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
>> Also, below is witnh permission from David Browne, explaining how you can have SQL Server execute
>> CLR code executing in its own process:
> Thanks,
> But if I understand the question, the OP has compiled and installed the assembly in SQL CLR. If
> so, he just needs to issue write a CLR stored procedure in .NET that implements the trigger logic.
> Accessing the assembly the OLE Automation stored procedures is no longer necessary. The trigger
> body just needs to invoke a CLR stored procedure.
> David

Calling a .Net application via a COM wrapper in SQL Server 2005

You should really go another route. Having SQL Server execute code hosted by
CLR was never supported
though spOACreate nor xp_cmdshell. I'd consider re-writing that com object a
s a CLR stored procedure
and you are in a supported environment. Below is my general "canned" respons
e:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's adress space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is witnh permission from David Browne, explaining how you can ha
ve SQL Server execute
CLR code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last week, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:EC73FFA6-3F3E-47EE-809E-18BF1D60A88E@.microsoft.com...
>I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
> the option COM Interop set to True. Once built, the VS2003 project solutio
n
> produces a .tlb file. A setup is produced (from within VS2003) that includ
es
> the dll and tlb files. These are installed on a PC (running on Windows
> 2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (w
ith
> the latest SQL Server 2000 Service Pack). A trigger fired from one of the
> tables in our own database (within SQL Server 2000) makes the calls to the
> COM wrapped assembly.
> CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
> FOR INSERT, UPDATE,DELETE
> AS
> DECLARE @.hr int
> DECLARE @.factory int
> DECLARE @.xalert int
> DECLARE @.response int
> DECLARE @.errMsg varchar(8000)
> DECLARE @.success bit
> EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUT
PUT
> select @.hr
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
> @.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactor
y',
> 'http://localhost:8080/axis/services/xalertsport'
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
> 'MyXalerts55_030506_ss', 1100
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.xalert , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> IF (@.success = 0)
> BEGIN
> EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> END
> EXEC @.hr = sp_OADestroy @.xalert
> EXEC @.hr = sp_OADestroy @.response
> EXEC @.hr = sp_OADestroy @.factory
>
> This works fine and we are able to create the objects and make the method
> calls on it.
> We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
> VS2005, using the same settings as before. A new setup was created and
> installed on a PC running on Windows 2000 Server and has SQL Server 2005 S
P1
> installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
> database and our own databse have been made "trustworthy". The assembly
> created above (with .net 2,.0) has been created within SQL Server, setting
> External_acesss (tried unsafe as well) permissions on it.
> We are using the same trigger as above and the same code within the trigge
r.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
>
> We have tried to put more COM Wrapper around the generated tlb , but that
> has not worked - giving the same error. Using .net 1.1 assembly (same one
as
> used for SQLServer 2000) , we get the same error. However, It is possible
to
> create other standard COM objects (like the SQLDMO objects), using the
> sp_oacreate. So, it may be something particular to .net assemblies being
> called (via the COM interop wrapper) from SQL Server 2005.I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
the option COM Interop set to True. Once built, the VS2003 project solution
produces a .tlb file. A setup is produced (from within VS2003) that includes
the dll and tlb files. These are installed on a PC (running on Windows
2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (wit
h
the latest SQL Server 2000 Service Pack). A trigger fired from one of the
tables in our own database (within SQL Server 2000) makes the calls to the
COM wrapped assembly.
CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
FOR INSERT, UPDATE,DELETE
AS
DECLARE @.hr int
DECLARE @.factory int
DECLARE @.xalert int
DECLARE @.response int
DECLARE @.errMsg varchar(8000)
DECLARE @.success bit
EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUTPU
T
select @.hr
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
@.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactory'
,
'http://localhost:8080/axis/services/xalertsport'
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
'MyXalerts55_030506_ss', 1100
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.xalert , @.hr
RETURN
END
EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
IF (@.success = 0)
BEGIN
EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
END
EXEC @.hr = sp_OADestroy @.xalert
EXEC @.hr = sp_OADestroy @.response
EXEC @.hr = sp_OADestroy @.factory
This works fine and we are able to create the objects and make the method
calls on it.
We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
VS2005, using the same settings as before. A new setup was created and
installed on a PC running on Windows 2000 Server and has SQL Server 2005 SP1
installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
database and our own databse have been made "trustworthy". The assembly
created above (with .net 2,.0) has been created within SQL Server, setting
External_acesss (tried unsafe as well) permissions on it.
We are using the same trigger as above and the same code within the trigger.
The trigger fails on the sp_oacreate line with the following error.
OLE Automation Error
HRESULT: 0x80131700
Source: ODSOLE EXtended Procedure
We have tried to put more COM Wrapper around the generated tlb , but that
has not worked - giving the same error. Using .net 1.1 assembly (same one as
used for SQLServer 2000) , we get the same error. However, It is possible to
create other standard COM objects (like the SQLDMO objects), using the
sp_oacreate. So, it may be something particular to .net assemblies being
called (via the COM interop wrapper) from SQL Server 2005.|||You should really go another route. Having SQL Server execute code hosted by
CLR was never supported
though spOACreate nor xp_cmdshell. I'd consider re-writing that com object a
s a CLR stored procedure
and you are in a supported environment. Below is my general "canned" respons
e:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's adress space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is witnh permission from David Browne, explaining how you can ha
ve SQL Server execute
CLR code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last week, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:EC73FFA6-3F3E-47EE-809E-18BF1D60A88E@.microsoft.com...
>I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
> the option COM Interop set to True. Once built, the VS2003 project solutio
n
> produces a .tlb file. A setup is produced (from within VS2003) that includ
es
> the dll and tlb files. These are installed on a PC (running on Windows
> 2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (w
ith
> the latest SQL Server 2000 Service Pack). A trigger fired from one of the
> tables in our own database (within SQL Server 2000) makes the calls to the
> COM wrapped assembly.
> CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
> FOR INSERT, UPDATE,DELETE
> AS
> DECLARE @.hr int
> DECLARE @.factory int
> DECLARE @.xalert int
> DECLARE @.response int
> DECLARE @.errMsg varchar(8000)
> DECLARE @.success bit
> EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUT
PUT
> select @.hr
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
> @.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactor
y',
> 'http://localhost:8080/axis/services/xalertsport'
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
> 'MyXalerts55_030506_ss', 1100
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.xalert , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> IF (@.success = 0)
> BEGIN
> EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> END
> EXEC @.hr = sp_OADestroy @.xalert
> EXEC @.hr = sp_OADestroy @.response
> EXEC @.hr = sp_OADestroy @.factory
>
> This works fine and we are able to create the objects and make the method
> calls on it.
> We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
> VS2005, using the same settings as before. A new setup was created and
> installed on a PC running on Windows 2000 Server and has SQL Server 2005 S
P1
> installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
> database and our own databse have been made "trustworthy". The assembly
> created above (with .net 2,.0) has been created within SQL Server, setting
> External_acesss (tried unsafe as well) permissions on it.
> We are using the same trigger as above and the same code within the trigge
r.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
>
> We have tried to put more COM Wrapper around the generated tlb , but that
> has not worked - giving the same error. Using .net 1.1 assembly (same one
as
> used for SQLServer 2000) , we get the same error. However, It is possible
to
> create other standard COM objects (like the SQLDMO objects), using the
> sp_oacreate. So, it may be something particular to .net assemblies being
> called (via the COM interop wrapper) from SQL Server 2005.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> You should really go another route. Having SQL Server execute code hosted
> by CLR was never supported though spOACreate nor xp_cmdshell. I'd consider
> re-writing that com object as a CLR stored procedure and you are in a
> supported environment. Below is my general "canned" response:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's adress space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is witnh permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
>
Thanks,
But if I understand the question, the OP has compiled and installed the
assembly in SQL CLR. If so, he just needs to issue write a CLR stored
procedure in .NET that implements the trigger logic. Accessing the assembly
the OLE Automation stored procedures is no longer necessary. The trigger
body just needs to invoke a CLR stored procedure.
David|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> You should really go another route. Having SQL Server execute code hosted
> by CLR was never supported though spOACreate nor xp_cmdshell. I'd consider
> re-writing that com object as a CLR stored procedure and you are in a
> supported environment. Below is my general "canned" response:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's adress space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is witnh permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
>
Thanks,
But if I understand the question, the OP has compiled and installed the
assembly in SQL CLR. If so, he just needs to issue write a CLR stored
procedure in .NET that implements the trigger logic. Accessing the assembly
the OLE Automation stored procedures is no longer necessary. The trigger
body just needs to invoke a CLR stored procedure.
David|||Yes, I fully agree. However, the first post indicates that this isn't what t
he OP had in mind. Or
perhaps the first post seemed a bit ambiguous on this:

> We are using the same trigger as above and the same code within the trigge
r.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
Thanks for making that option explicit, though. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:%23Hf3w3dmGHA.4536@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> But if I understand the question, the OP has compiled and installed the as
sembly in SQL CLR. If
> so, he just needs to issue write a CLR stored procedure in .NET that imple
ments the trigger logic.
> Accessing the assembly the OLE Automation stored procedures is no longer n
ecessary. The trigger
> body just needs to invoke a CLR stored procedure.
> David|||Yes, I fully agree. However, the first post indicates that this isn't what t
he OP had in mind. Or
perhaps the first post seemed a bit ambiguous on this:

> We are using the same trigger as above and the same code within the trigge
r.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
Thanks for making that option explicit, though. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:%23Hf3w3dmGHA.4536@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> But if I understand the question, the OP has compiled and installed the as
sembly in SQL CLR. If
> so, he just needs to issue write a CLR stored procedure in .NET that imple
ments the trigger logic.
> Accessing the assembly the OLE Automation stored procedures is no longer n
ecessary. The trigger
> body just needs to invoke a CLR stored procedure.
> David