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

Housekeeping blobs in a DB.

Hi,

VS2005

SQl workgroup 2005

I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.

- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.

Any tips, links or code is much welcome.

(my app will be acessing the DB though ADO .NET 3.0 c++/cli)

Cheers,

Andreas

WHat about using DELETE TOP(50 PERCENT) From SomeTable Where DateisOld ?

That would not delete every record which is old and will leave some of them still on the disk/Database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

Jens,

Hi, that was a good suggestion, I wasn′t aware of the TOP command. It sort of is useful maybe if its used with

a > 1000 to ensure there is atleast 1000 blobs in each category?

now I need to solve such a query

Any help is appriciated!

Cheers,

/Andreas

|||A quick sample for your asked query would be:

DELETE
Production.ProductProductPhoto
FROM Production.ProductPhoto P
INNER JOIN
(
SELECT ProductPhotoId,ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter
From Production.ProductPhoto
) SubQuery
ON P.ProductPhotoId = SubQuery.ProductPhotoId
WHERE SubQuery.Counter > 1000

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your swift reply, will try what youre suggesting.

so far i′ve come up with a CTE solution that′s close but not totally there

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category

ORDER BY Age DESC) AS rownum, ID FROM Imageblob)

DELETE FROM tempdata WHERE rownum < 1000;

hmm can you use DELETE TOP within a CTE? i didn′t get it to work.

Cheers,

/Andreas

|||

Whats your error message ?

THis works for me:

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter, ProductPhotoId FROM Production.ProductPhoto)

DELETE FROM tempdata WHERE counter > 1000;

Remember that you want to do a >1000 instead of a <1000

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Jens,

Hi,

ah, the <1000 was a typo.

Error message? I don′t really get the hang of where to put the TOP cmd.

Cheers,

/Andreas.

|||YOu don′t need the TOP if you use the TVF.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Ah,

I was toying with the idea to use TOP and Percentage rather than a fixed number to delete.

Cheers,

/Andreas

|||

Ok, is the problem solved then ? You can either delete a percentage or leave a threshold of rows in the table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I think so, ofc there are some more things I′ll try to add if I fail i′ll be back.

But thanks for your time and advice Jens!

Cheers

/Andreas

|||

Jens,

Ah, the phrase equal deletion is perhaps not accurate, I want to delete in the categories with most

rows. When i′ve created the CTE how can I from that deduce which Category in tempdata holds most

rows? Where do I fit a Count/Count_big?

And then delete from the category which has most rows and or delete more from the first, less from the second... etc

Code Snippet

count(age)

from Imageblob

Group by Category;

gives me rows per category, but with the CTE it gives me other number probably correct

based on the the tempdata is.

Cheers,

/Andreas

|||

The statement mentioned in your last post will elimenate the NULL values, therefore you probably have another count than the expected one of the CTE. For only deleting in the "biggest" category, you will have to query

Code Snippet

WITH tempdata AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter,
ProductPhotoId
FROM Production.ProductPhoto
)
DELETE tempdata
FROM tempdata F
INNER JOIN
(
SELECT TOP (1) COUNT(*),category FROM tempdata
GROUP BY category
ORDER BY COUNT(*)
) SubQueryForCount
ON SubQueryForCount.category = F.tempdata
WHERE counter > 1000;

Jens K. Suessmeyer

http://www.sqlserver2005.de

hosting SSIS packages where?

After i create a SSIS package, where can i put it or store it in the SQL SERVER 2005 so that i can execute it later on?

You can execute dtsx file directly or use dtexec command

It's better using Agent Job,and define schedule

|||

You could stored (I'm not wrong) it in three different places:

In the SSIS File System

In MSDB

In FAT/NTFS

You can execute a SSIS from a job, .net framework 2.0 code, VbScript, with DTEXECUI, etc..

|||Hi,

The option to do this is available once you have built you're project and you have done this whilst the CreateDeploymentUtility is True. All you then have to do is go to the deployment folder (usually in the bin directory) and run the .SSISDeploymentManifest file. This will give you all deployment options including where to store your package.

This link gves some info on how to execute the packages programmatically:

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Hope this is of use to you.

Cheers,

Grant|||

Grant Swan wrote:

Hi,

The option to do this is available once you have built you're project and you have done this whilst the CreateDeploymentUtility is True. All you then have to do is go to the deployment folder (usually in the bin directory) and run the .SSISDeploymentManifest file. This will give you all deployment options including where to store your package.

This link gves some info on how to execute the packages programmatically:

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Hope this is of use to you.

Cheers,

Grant

I don't understand what you just said... You can just upload a package to SQL Server (MSDB, SSIS File System, etc...) and execute it there via a number of methods. Some of those methods require that you have Integration Services installed on your local computer.|||He informing the poster that he can use the deployment utility to deploy his packages, and it will show him the different options available.|||

As I'm still coming up to speed on SSIS and have only worked with it for a couple of months, I can only speak of my own first hand experience. Others may take other approaches that are easier/better/simpler, etc.

First, I find that developing SSIS packages in the Business Intelligence/Visual Studio environment seems to work better when using the Visual Studio project/solution approach with the packages stored on a local development machine's hard disk. During this develop/test period I usually select the option to encrypt the package(s) with user credentials.

Then, when I've completed my development and testing and I'm ready to run the packages from SQL Server Agent for instance, I use the Save Copy As functionality to save the individual .dtsx packages to SQL Server. This Save Copy As functionality gets a little tricky to find as the option is only available when the package is open and a designer is actively selected. I do this mainly to remove dependencies on a particular user's credentials or a particular password, etc. from the security mix that is involved in getting these packages to run correctly from SQL Server Agent, etc. While I am using the Save Copy As functionality, to save it to SQL Server rather than the file system, one of the options presented is to change the security model for the package from encrypt with user credentials to that of letting SQL Server manage the security thru roles and permissions functionality inherent is SQL Server.

This works well for me in a design,program, test, deploy environment as it lets me make clear delineations between packages at the various stages, etc.

Hope this helps.

Steve Wells

|||

I got an error

"An OLE DB error 0x80004005 (unable to complete login process due to delay in opening server connection) occurred while enumerating packages. A SQL statement was issued and failed."

The below is my config:

Package location : SQL server

Server : Computer Name

USERNAME: < sql server login >

PASSWORD: <sql server pass>

package path: <I cannot select!! I got the above error>

Hosting DBO Nightmare going Live in 3 Days and counting

My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.

I've used a few sql scripts to change the owner using

Exec sp_changeobjectowner 'object', 'user'

and I've used

Grant Execute on object to user

These have changed ownership but the site still doesn't work

Does anyone know what to do to achieve this.

Any help greatly appreciated

Many Thanks

DuncanI hate to tell you, but our hosting operation does give our customers only DDLAdmin rights...

...and they have NO (!) problem creating entries belonging to the dbo user.

It is trivial. Just make sure you create them for the dbo user in the first place (i.e. EXPLICIT).|||Thanks Thona, this seems to set all the tables up fine but when I run dotnetnuke, it has a index out of bound error, I think this is happening because it is not creating any records in the table