Raster Catalog: query

Showing posts with label query. Show all posts

Wednesday, March 28, 2012

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Monday, March 26, 2012

Read the profiler output.

Wednesday, March 21, 2012

Read Consistency

What is the mechanism used by select statements to return point in time data?

I have a test setup - table t1 has 1000000 rows. A query (call q1) that selects all the rows (in NOLOCK mode and process them) takes 10 minutes. At the same time another process inserts another 1000000 rows into the same table t1. As expected, client that issued query q1 sees just 1000000 rows.

My understanding is that NOLOCK does not hold any locks. So, how did SQL Server know that it should not return the rows that are inserted after I issued the query q1? Some explanation or link to some whitepapers would be helpful.

Thanks

Unless you use the row-level versioning that was introduced in SQL Server 2005, there is no notion of 'point-in-time' data for the locking-based database engines for a certain fixed time in the past, until the transaction commits and only for the precise subset of the data read or written by the transaction and of the data that was intended to be read but it didn't exist when the transaction tried to access it.

Instead, the engine can provide an illusion of a serialized execution as if during the processing of transactions the view of the data accessed and touched by the transaction was 'frozen', i.e. the things that other transactions did, either occurred in the past, will occur in the future, or they don't matter if other transaction never read or wrote the data accessed and touched by our transaction. As a result the data that is seen by a single transaction ultimately can be viewed as a consistent slice of some relevant subset of entire database as of 'now' while the transaction is active and as of commit time after the transaction commits.

Having said that I realize that it might sound really cryptic and confusing - but this is how the things are done in the locking-based transaction processing systems. If you come from the Oracle world it will take some time to adjust to a different paradigm.

|||

Thanks Tengiz but ...

In my scenario, let us say that query q1 started at 10 AM and finished at 10:10 AM. At 10 AM it had 1000000 rows and by 10:05 AM other transactions inserted 1000000 rows more making it a total of 2000000 rows. Why did not SQL server return 2000000 rows to q1 client? Somehow SQL server knew to provide the rows that existed at 10 AM (that I call point in time data; may be wrong terminology?) and ignore the rows that were added after that point. What is the internal mechanism used by the engine to give that illusion? I am inclined to think that even though it does not create locks it might create some sort of semaphores, latches or tables in memory to keep track of the rows that need to be returned to q1 client. As you guessed, I am from Oracle background; may be you answered my question and it might take little longer to get it.

|||

Could you please be more specific describing you scenario? The fact that the query only returned the initial set of rows and didn't see the rows that were inserted after the query started does not really mean that the server somehow knows or takes into account the time when the rows were inserted.

Again, the things are different if you use the row-level versioning - you either do it be switching to the snapshot isolation (after enabling it for the database) or if you allow versioning-based read-committed isolation.

But assuming the you don't use the row-level versioning, depending on the existing indexes, the actual query plans, the key values that existed in the table before the insert and the key values inserted, the select query with the right timing could easily skip the newly inserted records, but it would have nothing to do with the read-consistency provided to the row-level versioning.

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

The database is not setup to use row versioning. Also, this is a data warehouse system. So, DML statements can come only from ETL. No other concurrent user is touching this table while this program is running. However my SSIS program that maintains this table opens two sessions (a reader and a writer) as I explained in step 4. My concern is that these two sessions stepping on each other.

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

Step 1:

CREATE TABLE Table1 (

Column1[int] NOT NULL,

Column2[int] NOT NULL,

Column3[bit] NOT NULL,

Column4[datetime] NOT NULL,

Column5[int] NOT NULL,

Column6[int] NOT NULL,

Column7[varchar](255) NULL,

Column8[int] NULL,

Column9[int] NULL,

Column10[int] NULL

CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (

[Column1] ASC,

[Column2] ASC,

[Column4] ASC,

[Column5] ASC,

[Column6] ASC )

)

Step 2:

INSERT INTO Table1

SELECT *

FROM Source_Table1

10,556,214 rows inserted.

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads

delete

from SourceStage.RealDB_StatusHistory

where Column1 % 2 = 0

5,288,119 rows deleted

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted

5,268,095 rows updated

I repeated the above steps without the clustered index and I get the same row counts. The row counts show no surprises which leads me to believe that there is some kind of lock. I have to make sure this works 100% before I put this code into production. The documentation leads me to believe that it does not work 100% of the time. If that is the case, I should be able to simulate a scenario where I get whacky row counts.

Here is what I think the reason for not getting whacky row counts in my setup. (a) when the clustered index is in place, it rebalances the tree during the delete operation. So the inserted rows go into new pages and SQL Server somehow knows to ignore them. (b) when there is no clustered index, then it sorts the data in Temp database before it is being fed to the SSIS. So data is essentially is sourced from the Temp database during pipeline operation of SSIS. Am I thinking in the right direction?

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

A quick answer to your question "can I use NOLOCK without any bad effects in this scenario?" is NO. The NOLOCK hint relaxes certain concurrency-related guarantees in the engine and essentially nullifies the notion of transactional consistency. It doesn’t mean that you will never get any consistency if you use NOLOCK, but you will not in general have predictable results.

I'm not an SSIS expert, so I'm not sure how SSIS really performs the 'insert else update' operation and I still don't quite get what you actually do in this scenario. But from the description of it looks like the plan does include spooling in tempdb for sort - the fast load option normally feeds data in through the BCP API which if the destination table is a clustered index assumes that that data needs to be sorted before it gets delivered to the destination. Hence, if the input provided by the SSIS is not sorted (there is a special hint that SSIS can specify in order to avoid extra sort) the then query optimizer adds the sort operator.

Spooling can certainly make it look like there indeed was some kind of 'read consistency' provided, but, again, depending on the actual query and specific conditions the optimizer is free to choose other options as well.

|||Thanks for the reply. Thinking about it further, when I don't use the NOLOCK hint, my SSIS package just waits forever. When I use NOLOCK hint, it seems to work fine. However, what if SQL Server does not honour the NOLOCK hint? My package might wait forever. So, I decided use the Lookup Transformation of SSIS rather than the Merge Join Transformation. Lookup Transformation can cache the data upfront before the Data Flow Task starts prosessing source rows. This way there is no contention.

Read Consistency

What is the mechanism used by select statements to return point in time data?

Thanks

|||

Thanks Tengiz but ...

|||

Thanks to Tengiz for your interest and perseverance in helping me out.

Step 0:

-- display isolation level

dbcc useroptions

isolation level = read committed

Step 1:

CREATE TABLE Table1 (
Column1[int] NOT NULL,
Column2[int] NOT NULL,
Column3[bit] NOT NULL,
Column4[datetime] NOT NULL,
Column5[int] NOT NULL,
Column6 [int] NOT NULL,
Column7[varchar](255) NULL,
Column8[int] NULL,
Column9[int] NULL,
Column10[int] NULL
CONSTRAINT [PKC_REALDB_StatusHistory] PRIMARY KEY CLUSTERED (
[Column1] ASC,
[Column2] ASC,
[Column4] ASC,
[Column5] ASC,
[Column6] ASC )
)

Step 2:

INSERT INTO Table1
SELECT *
FROM Source_Table1

10,556,214 rows inserted.

Step 3:

-- delete the rows to simulate unexpected results due to dirty reads
delete
from SourceStage.RealDB_StatusHistory
where Column1 % 2 = 0

5,288,119 rows deleted

Step 4:

Now, I have an SSIS package that does update else insert operation. It does SSIS left outer merge join to decide update verses insert. Since I use table lock in the destination component, the source component (one that feeds the data from the target table to do merge join), uses NOLOCK hint. The destination component uses fast load with a batch size of 1000 rows. The rows to be updated are saved to a empty intermediate table. A Transact-SQL UPDATE statement is used after the data flow is done to apply those into Table1.

5,288,119 rows inserted
5,268,095 rows updated

So the big question is, can I use NOLOCK without any bad effects in this scenario? If you believe this will lead to some dirty read scenario, how can I simulate it?

|||

Tuesday, March 20, 2012

Re: Left function causing Sum function not working??

Hi all,

My query won't add up the quantities after adding LEFT function in SQL statement can anyone see why?

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, SUM(dbo.tblBag_data.bag_quantity) AS qty_on_hand,
dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num,
dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
LEFT(dbo.tblShipping_sched.work_ord_num, 6) = LEFT(dbo.tblBag_data.work_ord_num, 6)
WHERE (dbo.tblShipping_sched.work_ord_num LIKE '343024%')
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num

''this s/b one record w/ summed qty
Work_order line_num qty_on_hand cust_num cust a_p_n a_c_n
343024-01 001 16540 32246 Davol Inc R1082061 R00295-059-70EPF-HM+
343024-01 001 27344 32246 Davol Inc R1082061 R00295-059-70EPF-HM+

Thanks for your input.because your GROUP BY specifies the individual dbo.tblBag_data.bag_quantity values

Re: help please

Dear all,

I have 3 column: date, time, stocks name, price

I have 2 questions:

1. what is the command (or query languange) to get the
the first and/or last observations for any given day (i know it can be done in
aggregate query, in Ms acces but can it be done in SQL server query as well?)?
e.g. I want to get the first and last price of the day for any particular stocks

2. how to calculate return with the following formula:
return=log P(t)-log P(t-1), where P(t) is price at
time t say 10 am and P(t-1) is price at one period
previous t say 9 am?

Regards

CharlyThe min and max functions will tell you the price ranges

eg select max(pricecolumn) from tablename where date='20040517'

select min(pricecolumn) from tablename where date='20040517'

If you want to be more selective look at the date/time setting you are using in your data and tailor the where command to select at that particular time

eg where date='2003-02-28 10:00:00.000'

Look at books online for the log function, and use selective where clauses for the times, ie where date='2004-05-17 10:00:00.000'|||select max(pricecolumn) from tablename where date='20040517'
broup by [stocks name]

Re: What is the syntax for isnull in the IIF function in the query statement

Hi all,

What is the syntax for isnull in the IIF function in the query statement for SQL Server2K?

I'm trying to create a view to get a total in a field. If the quantity is null I want to display 0 in that field.

Thanks!!

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, IIf(dbo.tblBag_data.bag_quantity IS NULL, 0,
dbo.tblBag_data.bag_quantity) AS qty_on_hand, dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name,
dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
dbo.tblShipping_sched.work_ord_num = dbo.tblBag_data.work_ord_num
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_numsql server doesn't support IIF, you have to use CASE

incorrect --

IIf(dbo.tblBag_data.bag_quantity IS NULL, 0, dbo.tblBag_data.bag_quantity)

correct --

case when dbo.tblBag_data.bag_quantity IS NULL then 0 else dbo.tblBag_data.bag_quantity end

Monday, March 12, 2012

RDO DataType query.

A colleague is trying to get some SQL Server data into MS Access, and is querying the table from a VB program.

She opens an rdoconnection to an SQLServer database. When testing recordset.rdocolumns("fieldName").type it gives -9 as the value. This does not relate to any of VB's listed RDO connection types. It also shows the rdocolumns("fieldName").size as being twice what the database shows when viewed through Microsoft Access. What is this datatype?

Any ideas?

I've found various lists for datatype eNums for ADO, but nothing on RDO (as it's obviously obsolete). Anyone out there got any ideas (other than "use something else")?

Thanks in advance

Chris.

export the data from sql server using dts

call the dts from the apps using dts run utility

use ado

or use replication

RDLC Reporting Service - setting up query parameters - is this possible?

Hi, I have my RDLC report, called on a ReportViewer, which receives a parameter (id of a column to filter the data), and the report receives this parameter very well, however, I want to use the value of this parameter to a query parameter of the dataset. On VS2005 we can asociate data sets to a report, but not a report parameter to a query parameter...is there any way to make this work?

I've heard that this is not possible using client report and report viewer, as print button. If I use a server report, will I have the print button on my application available?If not, how can I present the report with the print button available?

Thanks a lot!

You must use a server report to use reporting services built in print functionality.

For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

|||

dr_99:
You must use a server report to use reporting services built in print functionality.
For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

Yes I'm sure about that, thanks a lot!Only one problem, on IE the print button appears, but on Firefox does not appear, has anyone ideia what can cause this?It is set to visible!Thanks!

Friday, March 9, 2012

RDLC Client Report and query parameters and print button

Hi, this is my first post here. I hope to be helpful trying to help and not only asking questions arround here. After I have my report ready I will share here the total experience from top to bottom!But for now, here's the issue:

I'm building a RDLC Repor on my ASP.Net VB web application. I added the .rdlc file to the application and created a table to show lines of data binded from a dataset. The thing is:

- The DataSet expects a parameter @.intNumber, a identifier to get the correct data to display the correct report.

- I'm using ReportViewer to view the report, and by code I've passed a Report Parameter to the *.RDLC report with success, just like this:

Dim parms(0) As ReportParameter
parms(0) = New ReportParameter("intNumber", 37)
ReportViewer1.LocalReport.SetParameters(parms)

The present issue is the following:
I want to use that parameter sent to the report to be sent to the query of the DataSet as parameter to the query to return the data to fill the report. I've heard that this is not possible, just with report server...

Another issue is the print button, also heard that only can appear on report server...no way to display and work on RDLC reports?Very confused right now...these issues are stupid, MS tools should allow these operations, which are not efficient if this is not possibla on RDLC...

In report viewer local mode, your application is loading the DataSet, not the Report Viewer. Therefore you need to supply the parameter when calling your TableAdapter's Fill method.

-Albert

Wednesday, March 7, 2012

RDA unable to pull data

Hello -

I am trying to pull data from SQL Server 2000 database onto my Pocket PC and the simple query works on Northwind sample database but does not on another custom build database. I did test the statement in the SQL Query Analyzer and it works.

The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."

I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!

Thanks!

Moldau

Here is the code:

private void RdaPull()

{

try

{

//Create the database

if (File.Exists("\\My Documents\\Test.sdf"))

File.Delete(\\My Documents\\Test.sdf);

SqlCeEngine engine = new SqlCeEngine();

engine.LocalConnectionString = localConnection;

engine.CreateDatabase();

engine.Dispose();

//Initialize RDA Object

SqlCeRemoteDataAccess rda = null;

rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);

rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);

rda.Dispose();

MessageBox.Show("Done");

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.Message);

}

From the sample code you have dumped, I dont see any issue. Can you please elaborate on what are all the configurations you have at IIS, SQL Server, Device so that we know the environment. This is a very basic operation and it should not fail. It worries me hearing that a basic scenario like this fails in your case!

Please provider as much detail as you can so that we have a good understanding of the problem environment.

Thanks,

Laxmi

|||

I am facing the same issue. I have several published examples of RDA and have followed them exactly. As soon as the Pull method is called I receive the error:

28622 Internal Error: Failure setting up a non parameterized query, possible incorrect SQL query.

Has anyone discovered what may be causing this?

|||

I had exactly the same error

Failure setting up a non parameterized query, possible incorrect SQL query.

this error was caused by a mistake in my SQL query. i hope this will help some of you!

Reguards.

|||

Check your sqlcesa30.log file from browser.

In my case it was :

2007/04/08 00:09:31 Thread=1398 RSCB=36 Command=PULL Hr=80040E09 SELECT permission denied on object 'Stolik', database 'rms', schema 'dbo'. 229

I changed my anonymous user permissions and everything works fine now.

RDA unable to pull data

Hello -

The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."

I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!

Thanks!

Moldau

Here is the code:

private void RdaPull()

{

try

{

//Create the database

if (File.Exists("\\My Documents\\Test.sdf"))

File.Delete(\\My Documents\\Test.sdf);

SqlCeEngine engine = new SqlCeEngine();

engine.LocalConnectionString = localConnection;

engine.CreateDatabase();

engine.Dispose();

//Initialize RDA Object

SqlCeRemoteDataAccess rda = null;

rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);

rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);

rda.Dispose();

MessageBox.Show("Done");

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.Message);

}

Please provider as much detail as you can so that we have a good understanding of the problem environment.

Thanks,

Laxmi

|||

I am facing the same issue. I have several published examples of RDA and have followed them exactly. As soon as the Pull method is called I receive the error:

28622 Internal Error: Failure setting up a non parameterized query, possible incorrect SQL query.

Has anyone discovered what may be causing this?

|||

I had exactly the same error

Failure setting up a non parameterized query, possible incorrect SQL query.

this error was caused by a mistake in my SQL query. i hope this will help some of you!

Reguards.

|||

Check your sqlcesa30.log file from browser.

In my case it was :

2007/04/08 00:09:31 Thread=1398 RSCB=36 Command=PULL Hr=80040E09 SELECT permission denied on object 'Stolik', database 'rms', schema 'dbo'. 229

I changed my anonymous user permissions and everything works fine now.

RDA unable to pull data

Hello -

The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."

I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!

Thanks!

Moldau

Here is the code:

private void RdaPull()

{

try

{

//Create the database

if (File.Exists("\\My Documents\\Test.sdf"))

File.Delete(\\My Documents\\Test.sdf);

SqlCeEngine engine = new SqlCeEngine();

engine.LocalConnectionString = localConnection;

engine.CreateDatabase();

engine.Dispose();

//Initialize RDA Object

SqlCeRemoteDataAccess rda = null;

rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);

rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);

rda.Dispose();

MessageBox.Show("Done");

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.Message);

}

Please provider as much detail as you can so that we have a good understanding of the problem environment.

Thanks,

Laxmi

|||

I am facing the same issue. I have several published examples of RDA and have followed them exactly. As soon as the Pull method is called I receive the error:

28622 Internal Error: Failure setting up a non parameterized query, possible incorrect SQL query.

Has anyone discovered what may be causing this?

|||

I had exactly the same error

Failure setting up a non parameterized query, possible incorrect SQL query.

this error was caused by a mistake in my SQL query. i hope this will help some of you!

Reguards.

|||

Check your sqlcesa30.log file from browser.

In my case it was :

2007/04/08 00:09:31 Thread=1398 RSCB=36 Command=PULL Hr=80040E09 SELECT permission denied on object 'Stolik', database 'rms', schema 'dbo'. 229

I changed my anonymous user permissions and everything works fine now.

RDA unable to pull data

Hello -

The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."

I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!

Thanks!

Moldau

Here is the code:

private void RdaPull()

{

try

{

//Create the database

if (File.Exists("\\My Documents\\Test.sdf"))

File.Delete(\\My Documents\\Test.sdf);

SqlCeEngine engine = new SqlCeEngine();

engine.LocalConnectionString = localConnection;

engine.CreateDatabase();

engine.Dispose();

//Initialize RDA Object

SqlCeRemoteDataAccess rda = null;

rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);

rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);

rda.Dispose();

MessageBox.Show("Done");

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.Message);

}

Please provider as much detail as you can so that we have a good understanding of the problem environment.

Thanks,

Laxmi

|||

I am facing the same issue. I have several published examples of RDA and have followed them exactly. As soon as the Pull method is called I receive the error:

28622 Internal Error: Failure setting up a non parameterized query, possible incorrect SQL query.

Has anyone discovered what may be causing this?

|||

I had exactly the same error

Failure setting up a non parameterized query, possible incorrect SQL query.

this error was caused by a mistake in my SQL query. i hope this will help some of you!

Reguards.

|||

Check your sqlcesa30.log file from browser.

In my case it was :

2007/04/08 00:09:31 Thread=1398 RSCB=36 Command=PULL Hr=80040E09 SELECT permission denied on object 'Stolik', database 'rms', schema 'dbo'. 229

I changed my anonymous user permissions and everything works fine now.

Saturday, February 25, 2012

RDA Pull method not creating tables/inserting data

I can't see what is going on, this is the situation:

I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:

I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.

So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
---------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);

IList _tableNames = new ArrayList();
IList _queries = new ArrayList();

############
Code that prepares tables and queries
############

for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}

the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.

A few things here -

1. from pocket internet explorer on your device or emulator, do you get a correct diagnostic message when you use the url http://10.1.1.206/mobile/sqlcesa30.dll ? if not, then your first issue is you aren't successfully getting from device to the server.

2. your connection string is screwed up. try this instead: connectionString = @."Data Source = \Program Files\client\db\MobileDB.sdf"

3. you do realize that the database must exist already before the pull? and that none of those tables can exist when you call pull? (you need to drop all the tables you want to pull from the server before you call rda.Pull()

4. the values you are using for <user> and <password> must represent a valid SQL Server login as well as have permissions on the specific database you are pulling from <DB>

5. what are you using for primary keys on the tables you are pulling? IDENTITY columns are going to get you into trouble with multiple users pulling with tracking turned on. better to switch to uniqueidentifiers (GUIDs).

Try that much and let me know.

Darren

|||

To answer your questions:

1. I get "SQL Server Mobile Server Agent 3.0" when I try to browse to the URL

2. I will try it without the quotes.

3. Yes, I know the tables must not exists prior to PULL

4. I had made the SQL user a db_owner

5. GUIDS

|||

It did not work, I changed the connection string from:

"Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

"Data Source=\\Program Files\\client\\db\\MobileDB.sdf";

and it still didn't work

|||

if I understand correctly, you are not getting an exception, you're just not getting the tables created in the SQL mobile database after the pull? Are you just using a SELECT * FROM statement to pull each table? Maybe try reducing this to pulling a single table in case there is some issue with your logic to iterate through a collection of tables pulling each one. Also - not knowing the schema of the server side db, are there any constraints on those tables?

-Darren

|||

I just remembered something that I bet is your issue - when you install SQL Server, you have to specify an authentication mode for the server. By default, it is Windows Authentication. In your RDA connection string, you are using SQL Server authentication. As a result, you need to make sure your instance of SQL Server is set to "Mixed Mode (Windows Authentication and SQL Server Authentication).

Hope that helps.

Darren

|||Thanx, I'll try that|||we decided to give merge replication a shot, we may comeback to trying RDA later.

RDA pull error

When I try to do a pull I'm getting the following error message:

'Failure setting up a non parameterized query, possible incorrect SQL query.'

Now when I run this query in QA it works and returns me data. I'm also doing a pull on another table that works.

so I have this that works for one pull:

rda.Pull("Customers", 'select customerId, firstname, lastname, state, city from Customers", rdaConn, RdaTrackOption.TrackingOff);

and then I have this one that fails and gives me the above mentioned error:

rda.Pull("Sales", "select salesID, buyer, SaleDate from Sales', rdaConn, RdaTrackOption.TrackingOff);

and it fails.

another issue I'm having is, I can't run any rda.Pull() with RdaTrackOption.TrackingOn, any ideas on why I can't do that?

any help is greatly apprecated as for I've been going nuts on trying to figure this stuff out for the pass 2 weeks.

Anything to do with quotes? Your select starts with a double and ends in a single quote.|||

No, that was a typo when typing it in here. I should've just did a copy & paste in here.

RDA Error:Failure setting up a non parameterized query, possible incorrect SQL query

I receive this error :

Failure setting up a non parameterized query, possible incorrect SQL query

any 1 can help me plz?

here is my code :

string strDBFile = DBPath;

string strConnLocal = DBConnection;

string strConnRemote = "Provider=sqloledb; "

+ "Data Source=AMNEH; "

+ "Initial Catalog=SIS; "

+ "Integrated Security=SSPI;";

string strURL = "http://" + ipAddress + "/" + virtualDirectory + "/sqlcesa30.dll";

SqlCeRemoteDataAccess rdaNW = new SqlCeRemoteDataAccess();

try

{

rdaNW.LocalConnectionString = strConnLocal;

rdaNW.InternetUrl = strURL;

rdaNW.InternetLogin = "";

rdaNW.InternetPassword = "";

string select = "select * from tabel1 where field1=326";

rdaNW.Pull("tabel1",select,

strConnRemote,

RdaTrackOption.TrackingOnWithIndexes,

"ErrorLog");

}

catch (SqlCeException exSQL)

{

MessageBox.Show("HRESULT:" + exSQL.HResult.ToString() + ",\nNativeError:" + exSQL.NativeError.ToString() + ",\nMessage:" + exSQL.Message);

}

finally

{

rdaNW.Dispose();

}

There is no obvious error with your select query but there are a number of things that can be setup incorrectly when doing RDA. Did you remove the table from the SQL CE database before running this code? I have answered dozens of questions in this forum and on microsoft.public.sqlserver.ce on the topic of troubleshooting RDA. You can use Google Advanced Group Search to search for the posts that apply to RDA.

Darren

Monday, February 20, 2012

Rather Simple Logic - Need some advice

I have two tables and I want to validate that the results of a query
match the required structure. In this example, the Bad Query returns
two Row2, it therefore does not match the required structure.
Thanks for the help.
Dave
Actual Results from a Query (Good)
---
PartNo Row Description
6F23-1700034-AP32NC 1 V65-F001G
6F23-1700034-AP32NC 2 V65-S002G
6F23-1700034-AP32NC 3 V65-T032G
Actual Results from a Query (Bad)
---
PartNo Row Description
6F23-1700034-AP32NC 1 V65-F001G
6F23-1700034-AP32NC 2 V65-S002G
6F23-1700034-AP32NC 3 V65-T003G
6F23-1700034-AP32NC 2 V65-S002G
Required Structure
--
Row Description
1 1st Row
2 2nd Row
3 3rd RowA precise definition of 'match required structure' is needed to answer your
question.
IF (SELECT COUNT(*) FROM RequiredStructure) =
(SELECT COUNT(*) FROM ActualResults) AND
(SELECT COUNT(*) FROM RequiredStructure) =
(SELECT COUNT(*)
FROM(
SELECT Row FROM RequiredStructure
UNION
SELECT Row FROM ActualResults
) AS CompareResults
)
PRINT 'Matches'
ELSE
PRINT 'Does not match'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave" <davel@.here.ca> wrote in message
news:r0em72ln86fhtc5b9gammvvn72d2f7h68f@.
4ax.com...
>I have two tables and I want to validate that the results of a query
> match the required structure. In this example, the Bad Query returns
> two Row2, it therefore does not match the required structure.
> Thanks for the help.
> Dave
>
> Actual Results from a Query (Good)
> ---
> PartNo Row Description
> 6F23-1700034-AP32NC 1 V65-F001G
> 6F23-1700034-AP32NC 2 V65-S002G
> 6F23-1700034-AP32NC 3 V65-T032G
> Actual Results from a Query (Bad)
> ---
> PartNo Row Description
> 6F23-1700034-AP32NC 1 V65-F001G
> 6F23-1700034-AP32NC 2 V65-S002G
> 6F23-1700034-AP32NC 3 V65-T003G
> 6F23-1700034-AP32NC 2 V65-S002G
> Required Structure
> --
> Row Description
> 1 1st Row
> 2 2nd Row
> 3 3rd Row
>

Rate Table - Need Help To Display Data Horizontally

Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300

I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300

The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300

Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.i would suggest that you do this in the application layer|||I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps|||This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.

Wednesday, March 28, 2012

Read the profiler output.

Monday, March 26, 2012

Read the profiler output.

Wednesday, March 21, 2012

Read Consistency

Read Consistency

Tuesday, March 20, 2012

Re: Left function causing Sum function not working??

Re: help please

Re: What is the syntax for isnull in the IIF function in the query statement

Monday, March 12, 2012

RDO DataType query.

RDLC Reporting Service - setting up query parameters - is this possible?

Friday, March 9, 2012

RDLC Client Report and query parameters and print button

Wednesday, March 7, 2012

RDA unable to pull data

RDA unable to pull data

RDA unable to pull data

RDA unable to pull data

Saturday, February 25, 2012

RDA Pull method not creating tables/inserting data

RDA pull error

RDA Error:Failure setting up a non parameterized query, possible incorrect SQL query

Monday, February 20, 2012

Rather Simple Logic - Need some advice

Rate Table - Need Help To Display Data Horizontally

Raster Catalog

Blog Archive

About Me