Built-in db_execute right

I noticed you are taking suggestions for SP2 release of SQL 2005. One thing that would be nice would be a built in security role of db_execute that would give execute rights to all stored procedures in a database and/or db_executeUser to execute only user stored procedures in a database. It would simplify releasing for me. I always seem to forget to give the user account rights to execute everytime I add a new stored procedure.

Thanx,

I second the request..

Brien King
brien@.classic-soft.com

|||

Actually, there is a way to do this in 2005. I missed it through several passes of learning the security infrastructure and it's a pretty neat capability.

The first thing to understand is that you grant permissions on a securable to a principle. Once you've gotten there, it gets interesting when you realize that a Database User is a principle and a schema is a securable. So, if you were to put all of your procs into a single schema and then grant execute permissions on the schema to the user, they would be able to execute all of the procs in that schema. Now going one step up, you can also grant execute permissions on a database which means that any proc within the database can be executed by the user that you granted permission to.

Run the following example.

1. Connect to an instance as the sysadmin

2. Create a new login (for this example mine will be called test and will be a SQL Server login)

3. Add this login to the AdventureWorks database without any permissions

4. Open a new query window with a connection using the login created in step 2

5. Run exec AdventureWorks.dbo.usp_GetManagerEmployees 3

6. You should get a permissions error

7. Open a new query window and connect it as sysadmin (technically database owner authority which is the same account in this case). Switch database context to AdventureWorks.

8. Execute the following statement GRANT EXECUTE ON SCHEMA::dbo TO test

9. Now switch back to the other query window and rerun the proc. You should now get a result

10. But, if you try to execute any proc in the HumanResources schema, you will get a permissions error.

11. Now switch back to the sysadmin connection. Execute: REVOKE EXECUTE ON SCHEMA::dbo TO test

12. Verify that you no longer have permissions

13. Now execute GRANT EXECUTE TO test within the sysadmin connection

14. Switch back to the other connection. Re-execute the procedure and verify that you can now execute it. As a matter of fact, you can execute any proc in any schema within the database at this point

So, there really isn't a need to a database role to accomplish this, because it is already there. In fact, this capability can also be done with SELECT, INSERT, UPDATE, and DELETE, so it really should be used instead of db_datareader and db_datawriter. (I would expect to see these roles disappear in the future.)

Built table problem

Hi.

I am a beginner of SQL server.
I have a table namely Customer with a column "CustomerName" with data type varchar and length 50.
Column Name-Customer Name
Data Type- varchar
Length- 50.

May I know how to ENSURE that every customer's name only contain ALPHABET and strictly avoid the numeric character and
other characters such as "&, !,#...".

Please help. Thank you.See the BOL for Check Constraints !!!|||What about this idea?

create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(code+replicate('Z',50-datalength(code)) like replicate('[A-Z]',50) or
code+replicate('Z',50-datalength(code)) like replicate('[a-z]',50)
))|||Very Nice

INSERT INTO test(code) SELECT 'ABC'
INSERT INTO test(code) SELECT '123'
INSERT INTO test(code) SELECT 'AB.'|||INSERT INTO test(code) SELECT 'Brett Kaiser'|||Originally posted by Enigma

INSERT INTO test(code) SELECT 'Brett Kaiser'



Now it is even more simple:

drop table test
go
create table test(id int,
code varchar(50)
CONSTRAINT mycheck check(lower(replace(code,' ','Z')+replicate('Z',50-datalength(code))) like replicate('[a-z]',50)
)
)
go
INSERT INTO test(code) SELECT 'Brett Kaiser'|||That looks cool !!! :)

Built in report tool returns dollar symbol not pound symbol.

I can't be the only one to of noticed this but my local setting are all set to United Kingdom, but when using the in built reporting tool I always get dollar symbols.
The command I used to format the text box was the formatcurrency() express which states it will format the text in accordance with the settings in control panel. The only thing I can think of is that it is either using a system default setting (were on a domain with roaming profiles) or it uses the language as set on the SQL server (which I haven't checked yet).

Anyone else noticed this odd behavior or know where you type the expression pattern to make a custom currency format for UK?

I use a custom format entered in the Format code: box

￡#,##0.00

|||Yeah thats, the method I used in the end. Shame the formatcurrency() function doesn't do what it should out of the box.
Thank you. |||

Yep, I'd be interested to know why we can't default to local currency settings as with say Office programs...

Let me know if you find out :)

|||Yes, would be useful. I might google once I have some spare time. Just hope one of the developers of that section who might know passes over this.|||

You may want to read this: http://msdn2.microsoft.com/en-us/library/ms156493.aspx

The Report has a Language property which could be set to a static value, such as en-UK. Or you could set it to the current user's language by using an expression: =User!Language. Note: you can also override the report's language on individual textboxes by explicitly setting the textbox.Language property.

-- Robert

|||I can confirm that setting the language of the report does in fact fix the local symbol's. I never noticed this property as it is tucked away. To get to it you have to select it from the drop down menu in properties ( http://www.devstuff.eu/images/stories/msdn/ReportSelecter.JPG ). Then you can set report wide settings.

Interesting points about this :
Text boxes local settings does not override form default.|||Thanks guys.

Built in monitoring tools.

I am looking for a utility that will give the names of the tables (or the sql strings will do as well) that had data inserted, updated or deleted.
Is there anything like that built into SQL Server?Originally posted by access_dude
I am looking for a utility that will give the names of the tables (or the sql strings will do as well) that had data inserted, updated or deleted.

Is there anything like that built into SQL Server?

u can use sql profiler

built in function

How is it possible to show null if the field is empty?

i.e.
select
ID,
name
from
table1

it should show something like:
1, 'jo'
2, NULL
3, NULL
4, 'Jack'
...

Thanks

use nullif(column,'')

Code Snippet

Create Table #data (

[Id] int ,

[Name] Varchar(100)

);

Insert Into #data Values('1','jo');

Insert Into #data Values('2','');

Insert Into #data Values('3',' ');

Insert Into #data Values('3','');

Insert Into #data Values('4','Jack');

Select Id, nullif(name,'') From #data

|||

Try:

Code Snippet

select

ID,

nullif(ltrim(rtrim(name)), '') as [name]

from

table1

-- or

select

ID,

case when ltrim(rtrim(name)) = '' then NULL else [name] end as [name]

from

table1

AMB

|||

Kent,

I am not sure how COALESCE function will fit for this issue.

NULLIF function is the ANSI standard and supported by most of the RDBMS databases.

Built database with SQL Server Management Studio Express, how can I quikly add test data?

I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager.

I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums.

Please help.

Hi, did you see 'New Query' button in Management Studio? Click it, and a editor window will appear, just like in Query Analyzer.

Or you can press F8 to open 'Object Explorer' (under View menu), then you can manipulate database objects like you do in Enterprise Manager.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
string,[vbcol=seagreen]
> is
> it
SP[vbcol=seagreen]
> The
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:[vbcol=seagreen]
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
David G.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
string,
> is
> > there still an advantage to using stored procedures (over just executing
> it
> > directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let the
SP
> > build the Sql string. Either way though, the Select statement will be a
> > variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of case?
> The
> > result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> >
> >
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
> > string, is there still an advantage to using stored procedures (over
> > just executing it directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let
> > the SP build the Sql string. Either way though, the Select statement
> > will be a variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of
> > case? The result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> John Smith wrote:
>> Hey folks,
>> I've got a select statement that gets built dynamically in code.
>> If what I pass to a stored procedure is nothing but a string that
>> is a select statement, and then have the stored procedure execute
>> that string, is there still an advantage to using stored procedures
>> (over just executing it directly from code).
>> The other possibility is to pass other parameters to the SP and let
>> the SP build the Sql string. Either way though, the Select
>> statement will be a variable string that gets executed.
>> Is there still a case for using stored procedures in this type of
>> case? The result set size will range from 1 record to 50,000
>> records.
>> Thanks!
>> Having the SP build the SQL is fine. Just use sp_executesql to
>> execute the call, and if the query has parameters, then use the
>> parameter feature of sp_executesql to define them and pass them to
>> the SQL statement. That way, if similar statements get executed, the
>> plan will already be in cache.
>> Performance will be fine... assuming your queries are tuned properly.
>>
>> --
>> David G.
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
--
David G.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!
John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>
|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.
|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,[vbcol=seagreen]
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
string,[vbcol=seagreen]
> is
> it
SP
> The
>
|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>
|||John Smith wrote:[vbcol=seagreen]
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
David G.

Building a report on top of a basicHttpBinding WCF Service

Hello,

I'm currenlty stuck in a situation not unlike http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1910495&SiteID=1 .

I've built a service exposed as basicHTTPBinding with WCF and am trying to create a report using this service as a datasource.

I've used a ".svc" file to host my web service as part of an existing ASP.NET application.

In my dev environment, the service is accessible as ht tp://localhost:10827/IncidentListes.svc (which gives the "how to generate a client for this service" page) while ht tp://localhost:10827/IncidentListes.svc?wsdl returns the following WSDL for the service :

"

<?xml version="1.0" encoding="utf-8" ?>

- <wsdlefinitions name="IncidentSrv" targetNamespace="http://tempuri.org/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlnsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlnsoapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://tempuri.org/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsap="http://schemas.xmlsoap.org/ws/2004/08/addressing/policy" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msc="http://schemas.microsoft.com/ws/2005/12/wsdl/contract" xmlns:wsaw="http://www.w3.org/2006/05/addressing/wsdl" xmlnsoap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsa10="http://www.w3.org/2005/08/addressing" xmlns:wsx="http://schemas.xmlsoap.org/ws/2004/09/mex">

- <wsdl:types>

- <xsdchema targetNamespace="http://tempuri.org/Imports">

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd0" namespace="http://tempuri.org/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd1" namespace="http://schemas.microsoft.com/2003/10/Serialization/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd2" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine.IncidentsSecurite" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd3" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine" />

</xsdchema>

</wsdl:types>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_InputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateur" />

</wsdl:message>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_OutputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateurResponse" />

</wsdl:message>

- <wsdlortType name="IIncidentSrv">

- <wsdlperation name="ListePourUtilisateur">

<wsdl:input wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" message="tns:IIncidentSrv_ListePourUtilisateur_InputMessage" />

<wsdlutput wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateurResponse" message="tns:IIncidentSrv_ListePourUtilisateur_OutputMessage" />

</wsdlperation>

</wsdlortType>

- <wsdl:binding name="BasicHttpBinding_IIncidentSrv" type="tns:IIncidentSrv">

<soap:binding transport="http://schemas.xmlsoap.org/soap/http" />

- <wsdlperation name="ListePourUtilisateur">

<soapperation soapAction="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" style="document" />

- <wsdl:input>

<soap:body use="literal" />

</wsdl:input>

- <wsdlutput>

<soap:body use="literal" />

</wsdlutput>

</wsdlperation>

</wsdl:binding>

- <wsdlervice name="IncidentSrv">

- <wsdlort name="BasicHttpBinding_IIncidentSrv" binding="tns:BasicHttpBinding_IIncidentSrv">

<soap:address location="http://localhost:10827/IncidentListes.svc" />

</wsdlort>

</wsdlervice>

</wsdlefinitions>

"

1) Which URL should I use as the connection string in the dataset wizard ? With or without the "?wsdl" ?

2) How do I write the "<query>" block to obtain the results of the ListePourUtilisateur method as my dataset ?

Okay, this is solved now ... mainly after realizing that the "Cannot execute URL query" error dialog had a small "details" button that actually brought up some useful information.

The problem was actually a namespace incoherence between the WSDL and my <Query>. Once this was fixed, it worked like a charm.

|||

Hey Renaud,

Could you tell me what the exact problem was (what did you entered for Querystring), because we're having the same problem.

Thanks alot,

Jeroen.

|||

Hi Jeroen,

For the query string we simply use the URL of our ".svc" page.

For the query itself, we use the following style :

<Query>
<SoapAction>
http://Namespace/ServiceName </SoapAction>
<Method Namespace="http://Namespace"
Name="ServiceName">
<Parameters>

<Parameter Name="name">
</Parameter>

</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">MethodNameResponse{}/MethodNameResult{}/ObjectName{Fields}</ElementPath>
</Query>

Hope this helps,

Renaud

Building a report on top of a basicHttpBinding WCF Service

Hello,

I'm currenlty stuck in a situation not unlike http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1910495&SiteID=1 .

I've built a service exposed as basicHTTPBinding with WCF and am trying to create a report using this service as a datasource.

I've used a ".svc" file to host my web service as part of an existing ASP.NET application.

In my dev environment, the service is accessible as ht tp://localhost:10827/IncidentListes.svc (which gives the "how to generate a client for this service" page) while ht tp://localhost:10827/IncidentListes.svc?wsdl returns the following WSDL for the service :

"

<?xml version="1.0" encoding="utf-8" ?>

- <wsdlefinitions name="IncidentSrv" targetNamespace="http://tempuri.org/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlnsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlnsoapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://tempuri.org/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsap="http://schemas.xmlsoap.org/ws/2004/08/addressing/policy" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msc="http://schemas.microsoft.com/ws/2005/12/wsdl/contract" xmlns:wsaw="http://www.w3.org/2006/05/addressing/wsdl" xmlnsoap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsa10="http://www.w3.org/2005/08/addressing" xmlns:wsx="http://schemas.xmlsoap.org/ws/2004/09/mex">

- <wsdl:types>

- <xsdchema targetNamespace="http://tempuri.org/Imports">

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd0" namespace="http://tempuri.org/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd1" namespace="http://schemas.microsoft.com/2003/10/Serialization/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd2" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine.IncidentsSecurite" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd3" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine" />

</xsdchema>

</wsdl:types>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_InputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateur" />

</wsdl:message>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_OutputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateurResponse" />

</wsdl:message>

- <wsdlortType name="IIncidentSrv">

- <wsdlperation name="ListePourUtilisateur">

<wsdl:input wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" message="tns:IIncidentSrv_ListePourUtilisateur_InputMessage" />

<wsdlutput wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateurResponse" message="tns:IIncidentSrv_ListePourUtilisateur_OutputMessage" />

</wsdlperation>

</wsdlortType>

- <wsdl:binding name="BasicHttpBinding_IIncidentSrv" type="tns:IIncidentSrv">

<soap:binding transport="http://schemas.xmlsoap.org/soap/http" />

- <wsdlperation name="ListePourUtilisateur">

<soapperation soapAction="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" style="document" />

- <wsdl:input>

<soap:body use="literal" />

</wsdl:input>

- <wsdlutput>

<soap:body use="literal" />

</wsdlutput>

</wsdlperation>

</wsdl:binding>

- <wsdlervice name="IncidentSrv">

- <wsdlort name="BasicHttpBinding_IIncidentSrv" binding="tns:BasicHttpBinding_IIncidentSrv">

<soap:address location="http://localhost:10827/IncidentListes.svc" />

</wsdlort>

</wsdlervice>

</wsdlefinitions>

"

1) Which URL should I use as the connection string in the dataset wizard ? With or without the "?wsdl" ?

2) How do I write the "<query>" block to obtain the results of the ListePourUtilisateur method as my dataset ?

Okay, this is solved now ... mainly after realizing that the "Cannot execute URL query" error dialog had a small "details" button that actually brought up some useful information.

The problem was actually a namespace incoherence between the WSDL and my <Query>. Once this was fixed, it worked like a charm.

|||

Hey Renaud,

Could you tell me what the exact problem was (what did you entered for Querystring), because we're having the same problem.

Thanks alot,

Jeroen.

|||

Hi Jeroen,

For the query string we simply use the URL of our ".svc" page.

For the query itself, we use the following style :

<Query>
<SoapAction>
http://Namespace/ServiceName </SoapAction>
<Method Namespace="http://Namespace"
Name="ServiceName">
<Parameters>

<Parameter Name="name">
</Parameter>

</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">MethodNameResponse{}/MethodNameResult{}/ObjectName{Fields}</ElementPath>
</Query>

Hope this helps,

Renaud

Building a report on top of a basicHttpBinding WCF Service

Hello,

I'm currenlty stuck in a situation not unlike http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1910495&SiteID=1 .

I've built a service exposed as basicHTTPBinding with WCF and am trying to create a report using this service as a datasource.

I've used a ".svc" file to host my web service as part of an existing ASP.NET application.

In my dev environment, the service is accessible as ht tp://localhost:10827/IncidentListes.svc (which gives the "how to generate a client for this service" page) while ht tp://localhost:10827/IncidentListes.svc?wsdl returns the following WSDL for the service :

"

<?xml version="1.0" encoding="utf-8" ?>

- <wsdlefinitions name="IncidentSrv" targetNamespace="http://tempuri.org/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlnsoap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlnsoapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://tempuri.org/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsap="http://schemas.xmlsoap.org/ws/2004/08/addressing/policy" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msc="http://schemas.microsoft.com/ws/2005/12/wsdl/contract" xmlns:wsaw="http://www.w3.org/2006/05/addressing/wsdl" xmlnsoap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsa10="http://www.w3.org/2005/08/addressing" xmlns:wsx="http://schemas.xmlsoap.org/ws/2004/09/mex">

- <wsdl:types>

- <xsdchema targetNamespace="http://tempuri.org/Imports">

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd0" namespace="http://tempuri.org/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd1" namespace="http://schemas.microsoft.com/2003/10/Serialization/" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd2" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine.IncidentsSecurite" />

<xsd:import schemaLocation="http://localhost:10827/IncidentListes.svc?xsd=xsd3" namespace="http://schemas.datacontract.org/2004/07/Sncf.Dsit.Carto.Domaine" />

</xsdchema>

</wsdl:types>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_InputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateur" />

</wsdl:message>

- <wsdl:message name="IIncidentSrv_ListePourUtilisateur_OutputMessage">

<wsdlart name="parameters" element="tns:ListePourUtilisateurResponse" />

</wsdl:message>

- <wsdlortType name="IIncidentSrv">

- <wsdlperation name="ListePourUtilisateur">

<wsdl:input wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" message="tns:IIncidentSrv_ListePourUtilisateur_InputMessage" />

<wsdlutput wsaw:Action="http://tempuri.org/IIncidentSrv/ListePourUtilisateurResponse" message="tns:IIncidentSrv_ListePourUtilisateur_OutputMessage" />

</wsdlperation>

</wsdlortType>

- <wsdl:binding name="BasicHttpBinding_IIncidentSrv" type="tns:IIncidentSrv">

<soap:binding transport="http://schemas.xmlsoap.org/soap/http" />

- <wsdlperation name="ListePourUtilisateur">

<soapperation soapAction="http://tempuri.org/IIncidentSrv/ListePourUtilisateur" style="document" />

- <wsdl:input>

<soap:body use="literal" />

</wsdl:input>

- <wsdlutput>

<soap:body use="literal" />

</wsdlutput>

</wsdlperation>

</wsdl:binding>

- <wsdlervice name="IncidentSrv">

- <wsdlort name="BasicHttpBinding_IIncidentSrv" binding="tns:BasicHttpBinding_IIncidentSrv">

<soap:address location="http://localhost:10827/IncidentListes.svc" />

</wsdlort>

</wsdlervice>

</wsdlefinitions>

"

1) Which URL should I use as the connection string in the dataset wizard ? With or without the "?wsdl" ?

2) How do I write the "<query>" block to obtain the results of the ListePourUtilisateur method as my dataset ?

Okay, this is solved now ... mainly after realizing that the "Cannot execute URL query" error dialog had a small "details" button that actually brought up some useful information.

The problem was actually a namespace incoherence between the WSDL and my <Query>. Once this was fixed, it worked like a charm.

|||

Hey Renaud,

Could you tell me what the exact problem was (what did you entered for Querystring), because we're having the same problem.

Thanks alot,

Jeroen.

|||

Hi Jeroen,

For the query string we simply use the URL of our ".svc" page.

For the query itself, we use the following style :

<Query>
<SoapAction>
http://Namespace/ServiceName </SoapAction>
<Method Namespace="http://Namespace"
Name="ServiceName">
<Parameters>

<Parameter Name="name">
</Parameter>

</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">MethodNameResponse{}/MethodNameResult{}/ObjectName{Fields}</ElementPath>
</Query>

Hope this helps,

Renaud