Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. Honório
Hello Solli,
> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlClr
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblies.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.dll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of method...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Showing posts with label webservices. Show all posts
Showing posts with label webservices. Show all posts
Friday, March 30, 2012
How can I consume webservices on SQL 2005
Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. HonórioHello Solli,
> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlCl
r
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblie
s.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.d
ll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of meth
od...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy
.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. HonórioHello Solli,
> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlCl
r
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblie
s.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.d
ll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of meth
od...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy
.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql
Friday, March 9, 2012
How are transactions managed for web services
Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
Hello McGeeky,
> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGeeky
http://mcgeeky.blogspot.com
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
> Hello McGeeky,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
>
|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
Hello McGeeky,
> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGeeky
http://mcgeeky.blogspot.com
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
> Hello McGeeky,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
>
|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>
How are transactions managed for web services
Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McG
y
[url]http://mcgy.blogspot.com[/url]Hello McGy,
> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGy
[url]http://mcgy.blogspot.com[/url]
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
> Hello McGy,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
>|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McG
y[url]http://mcg
y.blogspot.com[/url]Hello McGy,> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McG
y[url]http://mcg
y.blogspot.com[/url]"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
> Hello McG
y,>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McG
y" <anon@.anon.com> wrote in messagenews:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McG
y> [url]http://mcg
y.blogspot.com[/url]>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
>|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McG
y[url]http://mcg
y.blogspot.com[/url]"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McG
y" <anon@.anon.com> wrote in message> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McG
y[url]http://mcg
y.blogspot.com[/url]"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McG
y" <anon@.anon.com> wrote in message> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McG
y" <anon@.anon.com> wrote in messagenews:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McG
y> [url]http://mcg
y.blogspot.com[/url]>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)