bulk insert problems

hi

situation is like this:

I have csv file I would like to import into table. However I have problems with code page 852 so I changed it to 850. Here is the command

bulk insert ticket_dump_remedy from 'c:\work\ticket_dump_remedy_040308.csv'
with
(
CODEPAGE = 850,
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)

After that execution ends with error:
Bulk insert data conversion error (type mismatch) for row 2, column 2 (Create Date).

Create Date is defined as datetime...

Example of file row is this one...
"Case ID+","Create Date","Type+"
"APHD00000031378","12/2/2003 10:13:00 AM","General"OK, so you've got bad data...

Load the data to a stage table that has varchar for all data types

The use ISNUMERIC and ISDATE to test to see the bad rows...

ALTER The table after the load to add an identity column to confirm the row the badat data is on|||thank you for your help and time

but this csv file comes every day and my goal is update database according to this file so I will have to look for another solution :(|||Do you have a process to audit the file?

How big is it?

Do you have headers and trailers with the data?

Since it's csv, does it have a column list in row 1?

If you make a stage table, all varchar, you can check the file out before you apply it.|||it's file about 3000 rows

unfortunatelly I don't have any process to check if the csv is ok...

I will have xls file with the same data so probably I will try to connect to xls file via ODBC and get data from there...hopefully it will work..

anyway thank you for your time|||I have tried the excel file and it's working... I converted csv to excel and than uploaded data from there and somehow it's working. Don't ask me how...

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));

i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.

-Darren

Bulk insert of long unicode strings

Here is the situation, please let me know if you have any tips:

..TXT files in a share at \\foo

SPROCS run daily parses of many things, including data on that share. The
other day, we encountered rows in the TXT files which looked like:

column1Row1data,column2Row1data
column1Row2data,column2Row2data

...etc..

However, column2 was about 6000 bytes of unicode. We are bulk inserting
into a table specifying nvarchar(4000). When it encounters high unicode
rows, it throws a truncation error (16).

We really need information contained in the first 200 bytes of the string in
column2. However, the errors are causing the calling SPROC to abort.
Please let me know if you have any suggestions on workarounds for this
situation. Ideally, we would only Bulk Insert a sub-section of column2 if
possible.

Thanks!
/TyTy (tybala on the server at hotmail.com) writes:
> Here is the situation, please let me know if you have any tips:
> .TXT files in a share at \\foo
> SPROCS run daily parses of many things, including data on that share. The
> other day, we encountered rows in the TXT files which looked like:
> column1Row1data,column2Row1data
> column1Row2data,column2Row2data
> ..etc..
> However, column2 was about 6000 bytes of unicode. We are bulk inserting
> into a table specifying nvarchar(4000). When it encounters high unicode
> rows, it throws a truncation error (16).
> We really need information contained in the first 200 bytes of the
> string in column2.

You can use a format file like this one:

8.0
3
1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
2 SQLNCHAR 0 0 "," 0 dummy ""
3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS

Here you defined the host file to have three fields: the first is a
200-character long fixed length field, the second is closed by a ,
and the third field is close by end-of-line. By specifying a 0 in
the sixth column in the format file for the second field, you specify
that this field is is not be imported into SQL Server.

You may want to change the collation what fits with the collation you
use in your database.

Note that this only works if all occurrances of the first field is
more than 200 characters. Would there be a record with a shorter
length of this field, it will steal characters from the second field.
(You would probably get an error when importing the file, as BCP will
not find the delimiter for the second field.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I wanted to thank you for answering my question. We successfully
implemented a variant of your solution last night. The assistance is much
appreciated.

/Ty

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9401F28A16A1CYazorman@.127.0.0.1...
> Ty (tybala on the server at hotmail.com) writes:
> > Here is the situation, please let me know if you have any tips:
> > .TXT files in a share at \\foo
> > SPROCS run daily parses of many things, including data on that share.
The
> > other day, we encountered rows in the TXT files which looked like:
> > column1Row1data,column2Row1data
> > column1Row2data,column2Row2data
> > ..etc..
> > However, column2 was about 6000 bytes of unicode. We are bulk inserting
> > into a table specifying nvarchar(4000). When it encounters high unicode
> > rows, it throws a truncation error (16).
> > We really need information contained in the first 200 bytes of the
> > string in column2.
> You can use a format file like this one:
> 8.0
> 3
> 1 SQLNCHAR 0 200 "" 1 a Finnish_Swedish_CS_AS
> 2 SQLNCHAR 0 0 "," 0 dummy ""
> 3 SQLNCHAR 0 0 "\r\n" 2 b Finnish_Swedish_CS_AS
> Here you defined the host file to have three fields: the first is a
> 200-character long fixed length field, the second is closed by a ,
> and the third field is close by end-of-line. By specifying a 0 in
> the sixth column in the format file for the second field, you specify
> that this field is is not be imported into SQL Server.
> You may want to change the collation what fits with the collation you
> use in your database.
> Note that this only works if all occurrances of the first field is
> more than 200 characters. Would there be a record with a shorter
> length of this field, it will steal characters from the second field.
> (You would probably get an error when importing the file, as BCP will
> not find the delimiter for the second field.)
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Bulk insert into table with more columns than data within file

Hey all

I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"\n' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error.

First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile.

If this is not possible, is it possible to bulk insert into a temp table?

Thanksyou can do this if you specify a format file. read up on bcp in BOL about format files.

alternatively, you could bulk insert into a staging table or temp table and then do insert/select from there.

finally, if you can generate the file over again, you can include a null like this (assuming comma separated:

1,2,3,,5,

note the extra commas. in this case, a null would be inserted in the 4th and 6th positions.

Bulk insert from textfile

Hello,

I'm having the following situation. To exchange data between software-programs we have to use textfiles. These files are like: "2100880000095500400600000329000 00000329000 WNOW0121102B 1121". I have to cut the textfiles into lines of 256 characters. Next thing i have to do is cutting that line in chunks (by a defined structure) and use these chunks to insert them into colums in SQL Server. Example, the line above will become something like: "INSERT INTO table (21, 008800000955, '004006', etc, etc) ".

The code now is: i've opened a connection, and then I'm doing an INSERT per row, but when i have to insert 800.000 rows like this, it's a real bottleneck. Is there any way to perform the INSERTS quicker ?? By a bulk or something ?? Can anyone help me ??

Check out BULK INSERT or the BCP tool in the documentation.

Bulk Insert -- Access denied issues - 2

Hi All

Same situation as described here , same issue.

SQL Server(SQL2005 on Windows2003) uses domain account. This domain account enabled to be trusted for delegation. Client connects to server using Windows auth. Client issues BULK INSERT with UNC path. Statement returns error:

Cannot bulk load because the file "\\Server\pub\file.txt" could not be opened. Operating system error code 5(Access is denied.)

SQL 2000 runs this statement successfully so statement and file are OK. Everyone has all permissions on network share. Domain account granted all permissions explicitly so there is no access troubles.

Audit show anonymous connections.

Question is - how to put delegation in work?

Thank you in advance,

Alexander Sinitsin

I don't think this is a problem with delegation. How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication? If it is Windows authentication then the service account credentials will not be used (change from SQL Server 2000) so the connected user / login needs to have the permissions to access the file. For SQL logins, the service account credentials will be used. So could you please describe your scenario?

1. What is the SQL Server service account?

2. Where is the data file located?

3. How does the user connect to SQL Server? SQL or Windows Auth?

4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

|||

Hi Umachandar Jayachandran!

Thanks for your answer.

>How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication?

Client connected using Windows authentication.

>service account credentials will not be used (change from SQL Server 2000)

Sure. Server should use client's credential to access file.

>so the connected user / login needs to have the permissions to access the file.

Connected user has full permissions to access the file. Even more, this user is a file owner-creator.

>1. What is the SQL Server service account?

SQL Server run under domain account. This domain account enabled to be trusted for delegation.

>2. Where is the data file located?

Data file located on some PC in network. Not on SQL Server locally.

>3. How does the user connect to SQL Server? SQL or Windows Auth?

Windows Auth.

>4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

Everybody has all permissions on the data file. Client connected has all permissions. Even SQL Server's domain account has all permissions. I can access this file under client's credentials without any problems.

As far as I can see, in this scenario SQL Server 2005 should use client credentials to access file. But when I run BULK INSERT, access audit show anonymous access, not client's access.

Best regards,

Alexander Sinitsin

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