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ó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/

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

How can i configure SQL Server for clustring servers?

Hi
We are developing bussiness software for tourism company. Our application is
using SQL DB on MSSQL Server. We have a new customer and they have 500 or
more users.Around 300 users will be work on local network and 200 users will
be connect from remote side. My problem is how can i configure the sql
server's for best performance. We have just one DB for apllication. Am i
configure the sql servers for clustring.? I am wating for your ansvers. Thank
you"Batur KILIÇ" <Batur KILI@.discussions.microsoft.com> wrote in message
news:CB47B648-A5DE-4F9D-A94F-00E5F4DE8972@.microsoft.com...
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users
will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers.
Thank
> you
Clustering is a fail-over mechanism, not a scalability mechanism.
May be simpler to tune your application and buy a bigger server.|||Batur KILIÇ wrote:
> Hi
> We are developing bussiness software for tourism company. Our application is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers. Thank
> you
Hi
Knowing that the customer has 500+ users doesn't tell a lot about the
load on the system...;-).
The first thing is to make sure that you design your application
properly and make the correct indexes etc.
I assume that you have some sort of idea on how the application performs
so based on that you should have an idea on how big a server you should
buy. Depending on the size, usage, design, complexity etc. of the
application, a dual proc server can be either way to small or really
overkill. Basically you should have enough memory and processor power
and a decent disk configuration. Then you can go on from that.
A decent disk configuration in my opinion will be to have enough disks
to create seperate arrays for OS files, Databasefile(s), logfile(s) and
backup. I'd then avoid RAID 5 for the logfiles and maybe for the
databasefiles (depending how much money you can spend) and then put
these on RAID 1 (or 10/01). Then buy 15 K disks rather than 10K and keep
in mind that the more spindles you have to read from the better (read)
performance you get.
With the above in mind, you'll be starting on a decent level. Then you
can start looking into specific areas if the performance isn't sufficient.
Regards
Steen Schlüter Persson
DBA

How can i configure SQL Server for clustring servers?

Hi
We are developing bussiness software for tourism company. Our application is
using SQL DB on MSSQL Server. We have a new customer and they have 500 or
more users.Around 300 users will be work on local network and 200 users will
be connect from remote side. My problem is how can i configure the sql
server's for best performance. We have just one DB for apllication. Am i
configure the sql servers for clustring.? I am wating for your ansvers. Than
k
you"Batur KILI" <Batur KILI@.discussions.microsoft.com> wrote in message
news:CB47B648-A5DE-4F9D-A94F-00E5F4DE8972@.microsoft.com...
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users
will
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers.
Thank
> you
Clustering is a fail-over mechanism, not a scalability mechanism.
May be simpler to tune your application and buy a bigger server.|||Batur KILI wrote:
> Hi
> We are developing bussiness software for tourism company. Our application
is
> using SQL DB on MSSQL Server. We have a new customer and they have 500 or
> more users.Around 300 users will be work on local network and 200 users wi
ll
> be connect from remote side. My problem is how can i configure the sql
> server's for best performance. We have just one DB for apllication. Am i
> configure the sql servers for clustring.? I am wating for your ansvers. Th
ank
> you
Hi
Knowing that the customer has 500+ users doesn't tell a lot about the
load on the system...;-).
The first thing is to make sure that you design your application
properly and make the correct indexes etc.
I assume that you have some sort of idea on how the application performs
so based on that you should have an idea on how big a server you should
buy. Depending on the size, usage, design, complexity etc. of the
application, a dual proc server can be either way to small or really
overkill. Basically you should have enough memory and processor power
and a decent disk configuration. Then you can go on from that.
A decent disk configuration in my opinion will be to have enough disks
to create seperate arrays for OS files, Databasefile(s), logfile(s) and
backup. I'd then avoid RAID 5 for the logfiles and maybe for the
databasefiles (depending how much money you can spend) and then put
these on RAID 1 (or 10/01). Then buy 15 K disks rather than 10K and keep
in mind that the more spindles you have to read from the better (read)
performance you get.
With the above in mind, you'll be starting on a decent level. Then you
can start looking into specific areas if the performance isn't sufficient.
Regards
Steen Schlter Persson
DBA

How can I check for Null or Empty in an Insert/Select Statement - example in Access

The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql

How can I change the default location of full text catalog?

Our current full text catalog default location is C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\FTData, we want change the default location to c:\data\MSSQL.5\FTData, so all the FT catalog will be created in this new location. Can anyone tell me how to do it?

Thanks

ShihChao

See SQL Server 2005 Books Online topic:

CREATE FULLTEXT CATALOG (Transact-SQL)

If you use T-SQL you can specify the path.

The topic states “IN PATH 'rootpath'

Is the root directory for the catalog. If rootpath is not specified, the new catalog will be located in the default directory specified at setup.”

Full-text catalogs must be created on a local hard disk drive associated with an instance of SQL Server. rootpath cannot be a relative path; it must reside on a drive on the same computer.

As you probably know you can specify the path when you use the UI. It does not default to any path.

|||

Also…you can use regedit at your own risk to change the default fulltext path

HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/MSSQL.x/MSSQLServer/FullTextDefaultPath – specify path

The restart SQL Server service

|||Thanks all all the replies

How can I add picture in microsoft sql server express 2005?

I would like to store small image in MSSQL server wich I can view by vb .net. For expample: I like to see each customer picture ID as I click on customer name from the customer table.

ThanksQuestion moved to SQL Server Forum.sql

How are you notified of mssql patches?

Hi,
Might be a strange question, but I'm struggeling with it for a while..
Is there any website/newsletter/newsfeed, that just informs about new SQL
Server (2000 and 2005) patches?
With the Security Bulletin I don't get all fixes, just a few ones - at the
moment I always actively search for updates to prepare a sql server
maintenance and I would like to be notified about every patch regarding sql
server 2000 or sql server 2005.
Thanks and regards
Andreas
Hi Ben,
thanks for your answer - that's exactly what I searched for :-)
regards
Andreas

How are you notified of mssql patches?

Hi,
Might be a strange question, but I'm struggeling with it for a while..
Is there any website/newsletter/newsfeed, that just informs about new SQL
Server (2000 and 2005) patches?
With the Security Bulletin I don't get all fixes, just a few ones - at the
moment I always actively search for updates to prepare a sql server
maintenance and I would like to be notified about every patch regarding sql
server 2000 or sql server 2005.
Thanks and regards
AndreasI find
[url]http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx[/ur
l] a good source of what patches are available..
In terms of knowing stuff like when sp's are released I rely on my rss
reader, I have a fair few feeds set up and they soon let you know when
something has come out.
"Andreas B" wrote:

> Hi,
> Might be a strange question, but I'm struggeling with it for a while..
> Is there any website/newsletter/newsfeed, that just informs about new SQL
> Server (2000 and 2005) patches?
> With the Security Bulletin I don't get all fixes, just a few ones - at the
> moment I always actively search for updates to prepare a sql server
> maintenance and I would like to be notified about every patch regarding sq
l
> server 2000 or sql server 2005.
> Thanks and regards
> Andreas|||Hi Ben,
thanks for your answer - that's exactly what I searched for :-)
regards
Andreas

How are you notified of mssql patches?

Hi,
Might be a strange question, but I'm struggeling with it for a while..
Is there any website/newsletter/newsfeed, that just informs about new SQL
Server (2000 and 2005) patches?
With the Security Bulletin I don't get all fixes, just a few ones - at the
moment I always actively search for updates to prepare a sql server
maintenance and I would like to be notified about every patch regarding sql
server 2000 or sql server 2005.
Thanks and regards
AndreasI find
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx a good source of what patches are available..
In terms of knowing stuff like when sp's are released I rely on my rss
reader, I have a fair few feeds set up and they soon let you know when
something has come out.
"Andreas B" wrote:
> Hi,
> Might be a strange question, but I'm struggeling with it for a while..
> Is there any website/newsletter/newsfeed, that just informs about new SQL
> Server (2000 and 2005) patches?
> With the Security Bulletin I don't get all fixes, just a few ones - at the
> moment I always actively search for updates to prepare a sql server
> maintenance and I would like to be notified about every patch regarding sql
> server 2000 or sql server 2005.
> Thanks and regards
> Andreas|||Hi Ben,
thanks for your answer - that's exactly what I searched for :-)
regards
Andreas