One more solution for a pagination

Let me know what you think about the following code:
DECLARE @.MaxIdValue int
DECLARE @.MaxSortFieldValue nvarchar(50)

SELECT TOP 1 @.MaxIdValue = [id], @.MaxSortFieldValue = [SortField]
FROM (
SELECT TOP PageNumber*RowsPerPage [id], [SortField]
FROM MyTable
WHERE (FilterCondition) ORDER BY [SortField], [id]
) T
ORDER BY [SortField] DESC, [id] DESC

SELECT TOP RowsPerPage * FROM MyTable
WHERE ([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]

This is a dynamic SQL and it should be easily fixable.
PageNumber, RowsPerPage, FilterCondition and SortField are going to be
the variables and will be based on the user's search
condition/criteria.
----------
Thanks for you attention."Kurzman" <max@.virtuman.com> wrote in message
news:d8f86969.0408051335.56e477a2@.posting.google.c om...
> Let me know what you think about the following code:
> DECLARE @.MaxIdValue int
> DECLARE @.MaxSortFieldValue nvarchar(50)
> SELECT TOP 1 @.MaxIdValue = [id], @.MaxSortFieldValue = [SortField]
> FROM (
> SELECT TOP PageNumber*RowsPerPage [id], [SortField]
> FROM MyTable
> WHERE (FilterCondition) ORDER BY [SortField], [id]
> ) T
> ORDER BY [SortField] DESC, [id] DESC
> SELECT TOP RowsPerPage * FROM MyTable
> WHERE ([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
> ([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
> ORDER BY [SortField], [id]
> This is a dynamic SQL and it should be easily fixable.
> PageNumber, RowsPerPage, FilterCondition and SortField are going to be
> the variables and will be based on the user's search
> condition/criteria.
> ----------
> Thanks for you attention.

What I think is that your syntax is wrong :-)

If you want a pagination solution, then the usual answer is "do it on the
client side" - this article may be useful:

http://www.aspfaq.com/show.asp?id=2120

Simon|||> What I think is that your syntax is wrong :-)
> If you want a pagination solution, then the usual answer is "do it on the
> client side" - this article may be useful:
> http://www.aspfaq.com/show.asp?id=2120
> Simon

The client side is't primary. The bottleneck of pagination is a server
side, especially for a big tables. From this point of view following
code will works slowly. Also it will works correct only if ArtistName
+ '~' + Title is unique.
ArtistName + '~' + Title
>= @.aname + '~' + @.title

Following code hasn't this problem:

([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]
If index [SortField], [id] exist it works fast.

P.S. Thanks for a link to intresting article.sql

One More Question On Running Parallel Queries

An Execute SQL task takes 1 min to run a statement "insert into Mytable select * from view_using_joins"

Output: 10,225 rows affected.

But a Dataflow task configured to fetch data from the same view_using_joins into MyTable takes hours to do the same.

Could you please explain why is it so ?

Thanks

Subhash Subramanyam

I am not surprised that the Execute SQL Task is quicker. When you're inserting from one table to another in the same database then SSIS isn't going to outperform the database engine.

I don't know why it is hours quicker. There isn't really enough information here to say. What destination adapter are you using? Is the package running on the same machine as the database? Are you doing transformations on the way?

-Jamie

|||

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

|||

Have you chosen Fast Load on your destination?

Thanks.

|||

Subhash512525 wrote:

Hi Jamie,

Thanks for your reply.

1) I am running packages on a different server.

2) Using OLEDB adapters for Source and Destination . Here Database as well as the server are same for Source and Destination

3) No transformations in between

Wonder if we have to do some extra settings to here to achieve the same duration as that of execute sql task ?

Thanks

Subhash Subramanyam

You're running the package on a different server? I'd suggest that's yur problem right there. The data is going to have to go over the network - obviously this is going to take time.

Also, in your destination are you inserting with Fast Load?

I say again, in this scenario the data-flow isn't going to outperform the Execute SQL Task.

-Jamie

|||

Great Jamie, you figured out.

If you don't mind spending few minutes here, I am coming back to my actual scenario.

I surely expect specific views from experts here for each of the questions here: Phil B, Rafael S, Darren G, Jwelch, JayH, Scott B, Ashwin S, Brian, Bob, Donald F and many others I am still not aware of.

Scenario:

1) My SSIS Packages are run at US server. scheduleld during Nights.

2) Each Package runs 6-8 queries each having Joins Parallelly pulling data from Oracle Database Source (UNIX) in Europe, Total Data extracted do not exceed 5 Million rows)

3) Destination Database is at US.

4) Network Bandwidth (2 Mbps)

Problem is that It almost takes ages to execute these Packages (Ranging from 25 hours to 30 hours)

Questions are:

1) Where should I expect to run the SSIS Packages to give a better performance?

2) How can I perform only incremental load (using Dataflow task) taking into consideration performance aspects? (Any links for this can help)

3) Does the overlap of the Schedules for SSIS packages afffect the performance?

4) Are there any limits on running number of queries in parallell to pull data from oracle source

5) Will it be the best way, If I spool the query results into flat files on a local system where the source (oracle database) runs at Europe and then ftp them to a shared server at US, which I can use it for importing into Destination table

Waiting for your reply,

Many Thanks and Regards

Subhash Subramanyam

|||Thanks Bob, Please give your answers for my below questions if don't mind.|||The more work you can do to prevent keeping the data transmission "pipe" open, the better.

Perform your source query in Europe, export that to a file, compress it, and then FTP it to the US. Then uncompress it, and load it with SSIS.

The idea is to keep your transmissions across "the pond" as short as possible.|||

From your question #2, I'm assuming you are pulling all rows every night. As Phil mentioned, you want to minimize how much data you are actually moving, so I'd definately make this incremental. A common way to implement that is by checking modified dates on the source system via a WHERE clause in your source SELECT statements. Store the range of modified dates that you retreive, and when the package is run the next night, start from the end of the previous range.

If you don't have modified dates in the source system, consider adding them. Alternatives are using triggers to track changes, or using a change data capture tool - I believe Oracle has one, and SQL Server will have one with SQL Server 2008.

|||

One more question:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

Still expecting suggestions and the views of rest of the experts for six questions listed here.

Regards

Subhash Subramanyam

|||

Subhash512525 wrote:

6) If I have 6-8 queries running in parallel, Whether having a common connection Manager (for an Oracle source) for all performs better or having Distinct Connection Manager performs better ?

It depends Using a single one should result in the same performance as having several, assuming you are not using RetainSameConnection on them. Having a single connection manager doesn't mean that SSIS won't open multiple connections to the database. A Connection Manager manages multiple connections to the database, unless you force it to use only a single connection with RetainSameConnection.

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

|||

jwelch wrote:

A related note - in your scenario, have you tested whether performance is better if you run all queries sequentially or in parallel (by using precedence constraints on the data flow tasks)?

Jwelch, This seem more practical. I'll test this and let you know..

Thanks

Subhash

one line of results

I am trying to do a select statement where the results show up in one line like a,b,c,d,e...

I think I'm on the right track with the following code, but I have no idea what direction to go in. (I think my notes are not totally correct, but heck they're my notes. :-))

@.UM is getting wiped out after each into @.UM, how do I make it add to @.UM

declare @.UM varchar(3000) --declares variable
DECLARE abc CURSOR FOR --declares object for recordset
SELECT CONDCD FROM IBACOSTOCK GROUP BY CONDCD ORDER BY CONDCD
OPEN abc --stores results in recordset variable
FETCH NEXT FROM abc --grabs one line from recordset
INTO @.UM --stores line into variable
SELECT CONDCD FROM IBACOSTOCK GROUP BY CONDCD ORDER BY CONDCD
WHILE (@.@.FETCH_STATUS = 0) --as long as there are records in the recordset
begin
FETCH NEXT FROM abc --grabs next line from recordset
INTO @.UM --stores new line into variable
end
SELECT @.um
CLOSE abc --go back to while statement
DEALLOCATE abc --erase recordset
GOHow about...

DECLARE @.UM

SET @.UM = ''

SELECT @.UM = @.UM + CONDCD
FROM IBACOSTOCK
GROUP BY CONDCD ORDER BY CONDCD|||Yeah. I'm slow. I looked at my code afterwards and summed it up, and that's what I got. Thanks!

One dropdownlist depend from other.

Hello, I need to know how can I pass two parameters, One select depends from what I selected on the first one. I did it once but I just dont remember

You are looking for a dynamic parameter ( or dynamic dropdown) walk through or " how to".

Ill use the example of store locations and employees located in each store.

Fist what you would do is create your data sources for your drop downs

==================================== Data source "stores"

Select StoreID,

StorelocationName

From storetable

===================================

The second table will have the employee info in it . In this statement will be a parameter in the where clause the depends on the out put of the first data source

======================================== data source "employee"

select EmployeeName,

EmployeeID

from sometable

where storeID = @.storeID

========================================

after creating this data sources you will notice that a new parameter has been created in the report labeled "StoreID".

you will need to use a use the "stores" data source. Make the value field the storeID field. Do the same for the employee data source.

On the Data Tab you will see a "..." button on for each data source press it and go to the parameters tab for the data source employee. Make sure the @.storeID parameter is listed.

this should help you figure out the rest

hope it helps

On what basis sql server sorts the rows

Hi,

When I execute the following statement:

Select * from table1;

On what basis, SQL server decides the sequence of these rows?

I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.

Thanks veyr much.

Regards,

Tim

hi Tim,

you always have to provide a sort (ORDER BY) as the engine can return data in no actual order at all.. data is scanned with IO operations that are not limited to a "physical matter", so, without an ORDER BY clause, they are returned in the order they are read.. if you have a multi cpu machine, different processors can get data in "whatever order" and merged in the actual results... usually the "physical order" of a clustered table (a table with a clustered index) is used, but, again, that order is not guaranteed.. if you need (as you usually do) a particular order, whatever it could be, you have to provide that "hint" to the query processor... this is even more "important" if you have to do it in chunck (where you should use the ROW_NUMBER() OVER( ORDER BY orderCol) new clause of SQL Server 2005).. this obviously makes the query "heavier", as the result must be first generated and then ordered, but gives you the "real" taste of correct data and not data found over again and again in the successive calls..

remember that the ORDER BY clause is "cursor clause" and not part of the actual query.. logically, it's the "last part" of a complete plan, where the actual query result is passed to a cursor operation to sort data as desired..

regards

|||

If you do not use an ORDER BY clause, SQL Server will produce the data in whatever order it deems efficient.

At times, that may be the order in which the data has been put in the table -but that is just a temporal fluke. There is no guarantee that you will get the data in the same order the next time you query.

To control the presentation, you MUST use an ORDER BY statement.

Here are some other ideas and help about what is often referred to as 'paging' queries:

Paging Queries
www.aspfaq.com/2120

|||

Thanks very much.

Regards,

Tim

On delete trigger for a view

Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview]
FOR DELETE
AS
Declare @.user_name sysname, @.msg varchar(3000)
select @.user_name = name
from deleted
set @.msg = 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur)
values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur) values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m" wrote in message news:a388fd=78.0309300241.4462ca59@.posting.google.com...Hi!I have a view defined in MANAGE database as:CREATE VIEW dbo.sysdatabasesview ASSELECT *FROM master.dbo.sysdatabases WITH (nolock)GOTrying to place a =trigger on it:CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name =sysname, @.msg varchar(3000) select @.user_name =3D name from deleted =set @.msg =3D 'Delete database ' + @.user_name + ' on server ' =+@.@.servername + ' from host ' + host_name()insert into MANAGE..MAIL (recipient, =subject, message, occur) values ('myemail@.domain.local', ='Delete datadase', @.msg, getdate()) Get an error:Error =208: Invalid object name 'dbo.sysdatabasesview'What is wrong?Thanks.

---

--=_NextPart_000_0012_01C3872D.EA0155E0--sql

OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

OLEDB Source to Flat File

Hi,

I'm using an OLEDBSource to select some data and then putting to in a Flat File destination.

However, when I look at the data in the OLEDBSource, they′re like this:

1. id

2. name

3. address

...but in the flatfile it comes out in the wrong order.

How can I fix this?

Thank you so much.

Create the columns in the flat file connection manager in the order you need them to be in.

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.sql

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.

OLEDB source - Use table or select only columns needed.

Hi All,

With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?

I cannot see any difference in performance between the two.

Thanks.

Always always always use a SQL command so as to avoid the situation documented here: http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx I can't stress this enough.

Also check #4 here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx Basically, only pull in the data that you need otherwise performance will suffer.

-Jamie

|||Thanks Jamie but...

I would still like to know the underlying reason it is bad :)

Accepted that Select * is bad due to many reasons but:
My "tables" are views which themselves only select columns required for the data flow and nothing more.

Basically I am being lazy - I write the columns out in the view and don't _really_ want to write them again in SSIS :)

From a performance point of view, the above method is exactly the same either way. I cannot and have not seen what you described.

Will play around some more and try find a reason (unless someone wants to save us the trouble....)|||

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

|||

Jamie Thomson wrote:

Well if nothing else I would do it in the interests of best practice. And also cos I'm picky - I hate seeing a selected table rather than a SQL statement :)

-Jamie

A counter to that is I hate seeing any form of SQL in SSIS. Rather have the logic in a view / proc or just pull from the table. Make life easier when looking for bugs.
(yes, you could make a rule such as "do not use anything more that Select *" :)|||

Let's agree to disagree! :)

-J

OLEDB Datasources and parameters

I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.

Dick Campbell

I've never had any problems using parameters in OLEDB Sources. How are you defining the placeholders for your parameters? For OLEDB they should be a single "?".
A sample SQL statement would look like the following:
Select Col1, Col2 from MyTable where MyDate between ? and ?
The first ? would map to Parameter0 and the second would map to Parameter1.
Larry Pope
|||I am using ? as you suggest but I am calling an inline table valued function. The format is "select * from function(?,?,?) as function".

OLEDB consumer and for xml SELECT

Hi,
I have a select statement which retrieve data in xml format (FOR XML
AUTO option). When I run this statement from a client using an OLEDB
consumer template for the table, I get the data BUT it does not look
right... Here is a sample:
suppose I run the following statement:
SELECT StateID,
RTRIM(StateCode) AS StateCode,
RTRIM(StateName) as StateName,
RTRIM(Country) as Country
FROM State
FOR XML AUTO
This sql will generate the following result if run from SQL Query Analyzer:
..........................................
<State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
<State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
<State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
........... etc.
When I run the same query from a client using an OLEDB consumer template, I
get a string with a lot of nulls, the xml format is no longer there, there
are some unprintable chars, etc. The odd thing is that the data is there! It
just is not in the right format!?
Does anyone know what is going on?
Thanks,
George.Did you use the CommandStream interface? This looks like the binary format
that is being returned if you use the rowset interface. Using the
CommandStream interface will be giving you the stream in parseable XML.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a select statement which retrieve data in xml format (FOR XML
> AUTO option). When I run this statement from a client using an OLEDB
> consumer template for the table, I get the data BUT it does not look
> right... Here is a sample:
> suppose I run the following statement:
> SELECT StateID,
> RTRIM(StateCode) AS StateCode,
> RTRIM(StateName) as StateName,
> RTRIM(Country) as Country
> FROM State
> FOR XML AUTO
> This sql will generate the following result if run from SQL Query
> Analyzer:
> ..........................................
> <State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
> <State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
> <State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
> ........... etc.
> When I run the same query from a client using an OLEDB consumer template,
> I get a string with a lot of nulls, the xml format is no longer there,
> there are some unprintable chars, etc. The odd thing is that the data is
> there! It just is not in the right format!?
> Does anyone know what is going on?
> Thanks,
> George.
>|||Michael,
Thanks. No I did not use ICommandStream. The database access is done
using a stored procedure, and the OLEDB client code is generated by the
wizard. That creates a class ready to run the stored procedure and return
the result set.
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Did you use the CommandStream interface? This looks like the binary format
> that is being returned if you use the rowset interface. Using the
> CommandStream interface will be giving you the stream in parseable XML.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||I assume that this is the problem. If the stored proc generates a FOR XML
result, your OLEDB code has to use the command stream and not the normal way
of retrieving a relational rowset. FOR XML results are generating an XML
stream and not a rowset after all...
The Books Online should have some sample code snippets.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks. No I did not use ICommandStream. The database access is done
> using a stored procedure, and the OLEDB client code is generated by the
> wizard. That creates a class ready to run the stored procedure and return
> the result set.
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||Michael,
Thanks. Here is an answer I got in oledb forum:
.........................................................
...................
Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
so there is no problem.
You would see the problem if you did:
SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
And LOOPBACK is a linked server set up with SQLOLEDB.
In SQL 2005, there is a new command-line tool SQLCMD which is implemented
with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
issue a FOR XML query, all you get is a bunch of hex digits. I've submitted
a bug report for that. I wonder how they will fix it...
.........................................................
.........................................................
.........
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>I assume that this is the problem. If the stored proc generates a FOR XML
>result, your OLEDB code has to use the command stream and not the normal
>way of retrieving a relational rowset. FOR XML results are generating an
>XML stream and not a rowset after all...
> The Books Online should have some sample code snippets.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
>|||Correct. But please note that the OPENQUERY always requests a rowset and not
a CommandStream.
If you code against OLEDB yourself, you can use the ICommandStream and get
the XML back as a nice XML character stream. Were you able to try that?
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Michael,
> Thanks. Here is an answer I got in oledb forum:
> ........................................................
....................
> Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
> so there is no problem.
> You would see the problem if you did:
> SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
> And LOOPBACK is a linked server set up with SQLOLEDB.
> In SQL 2005, there is a new command-line tool SQLCMD which is implemented
> with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
> issue a FOR XML query, all you get is a bunch of hex digits. I've
> submitted
> a bug report for that. I wonder how they will fix it...
> ........................................................
.........................................................
..........
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||Michael,
Thanks. I am using the class created by the OLEDB wizard to add the
consumer template. That has an ICommandStream and I can read the data but it
is the same. Do you have a sample somewhere showing how to use
ICommandStream with a class generated by the wizard?
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ea$6uuyDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Correct. But please note that the OPENQUERY always requests a rowset and
> not a CommandStream.
> If you code against OLEDB yourself, you can use the ICommandStream and get
> the XML back as a nice XML character stream. Were you able to try that?
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
>

OLEDB consumer and for xml SELECT

Hi,
I have a select statement which retrieve data in xml format (FOR XML
AUTO option). When I run this statement from a client using an OLEDB
consumer template for the table, I get the data BUT it does not look
right... Here is a sample:
suppose I run the following statement:
SELECT StateID,
RTRIM(StateCode) AS StateCode,
RTRIM(StateName) as StateName,
RTRIM(Country) as Country
FROM State
FOR XML AUTO
This sql will generate the following result if run from SQL Query Analyzer:
...................................... ......
<State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
<State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
<State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
............ etc.
When I run the same query from a client using an OLEDB consumer template, I
get a string with a lot of nulls, the xml format is no longer there, there
are some unprintable chars, etc. The odd thing is that the data is there! It
just is not in the right format!?
Does anyone know what is going on?
Thanks,
George.
Did you use the CommandStream interface? This looks like the binary format
that is being returned if you use the rowset interface. Using the
CommandStream interface will be giving you the stream in parseable XML.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a select statement which retrieve data in xml format (FOR XML
> AUTO option). When I run this statement from a client using an OLEDB
> consumer template for the table, I get the data BUT it does not look
> right... Here is a sample:
> suppose I run the following statement:
> SELECT StateID,
> RTRIM(StateCode) AS StateCode,
> RTRIM(StateName) as StateName,
> RTRIM(Country) as Country
> FROM State
> FOR XML AUTO
> This sql will generate the following result if run from SQL Query
> Analyzer:
> ...................................... .....
> <State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
> <State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
> <State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
> ........... etc.
> When I run the same query from a client using an OLEDB consumer template,
> I get a string with a lot of nulls, the xml format is no longer there,
> there are some unprintable chars, etc. The odd thing is that the data is
> there! It just is not in the right format!?
> Does anyone know what is going on?
> Thanks,
> George.
>
|||Michael,
Thanks. No I did not use ICommandStream. The database access is done
using a stored procedure, and the OLEDB client code is generated by the
wizard. That creates a class ready to run the stored procedure and return
the result set.
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Did you use the CommandStream interface? This looks like the binary format
> that is being returned if you use the rowset interface. Using the
> CommandStream interface will be giving you the stream in parseable XML.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||I assume that this is the problem. If the stored proc generates a FOR XML
result, your OLEDB code has to use the command stream and not the normal way
of retrieving a relational rowset. FOR XML results are generating an XML
stream and not a rowset after all...
The Books Online should have some sample code snippets.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks. No I did not use ICommandStream. The database access is done
> using a stored procedure, and the OLEDB client code is generated by the
> wizard. That creates a class ready to run the stored procedure and return
> the result set.
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>
|||Michael,
Thanks. Here is an answer I got in oledb forum:
...................................... ...................................... ..
Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
so there is no problem.
You would see the problem if you did:
SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
And LOOPBACK is a linked server set up with SQLOLEDB.
In SQL 2005, there is a new command-line tool SQLCMD which is implemented
with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
issue a FOR XML query, all you get is a bunch of hex digits. I've submitted
a bug report for that. I wonder how they will fix it...
...................................... ...................................... ...................................... ............
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>I assume that this is the problem. If the stored proc generates a FOR XML
>result, your OLEDB code has to use the command stream and not the normal
>way of retrieving a relational rowset. FOR XML results are generating an
>XML stream and not a rowset after all...
> The Books Online should have some sample code snippets.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
>
|||Correct. But please note that the OPENQUERY always requests a rowset and not
a CommandStream.
If you code against OLEDB yourself, you can use the ICommandStream and get
the XML back as a nice XML character stream. Were you able to try that?
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Michael,
> Thanks. Here is an answer I got in oledb forum:
> ...................................... ...................................... .
> Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
> so there is no problem.
> You would see the problem if you did:
> SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
> And LOOPBACK is a linked server set up with SQLOLEDB.
> In SQL 2005, there is a new command-line tool SQLCMD which is implemented
> with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
> issue a FOR XML query, all you get is a bunch of hex digits. I've
> submitted
> a bug report for that. I wonder how they will fix it...
> ...................................... ...................................... ...................................... ...........
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>
|||Michael,
Thanks. I am using the class created by the OLEDB wizard to add the
consumer template. That has an ICommandStream and I can read the data but it
is the same. Do you have a sample somewhere showing how to use
ICommandStream with a class generated by the wizard?
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ea$6uuyDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Correct. But please note that the OPENQUERY always requests a rowset and
> not a CommandStream.
> If you code against OLEDB yourself, you can use the ICommandStream and get
> the XML back as a nice XML character stream. Were you able to try that?
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
>

OLEDB AS400 Pipeline threads

A simple dataflow :

Data source OLEDB AS400 : - data access sql command from variable

SELECT 'DEN' AS "Company","TDEN.IHD".* FROM "TDEN.IHD" WHERE (ORDNI1 > 48960)

Data destination : OLEDB Sql Server File

- No Problem when Data Source is executed As Sql command

Purpose :

Load (new) data from AS400-files and load them in the corresponding sql server table. Therefore each day the maximum ordernumber in the sql database is searched And given to a script which makes an sql command user::SqlSelect.

All This this to prevent loading each day all records again.

I choose to make the data access by an variable because I have more then one company with the same formatted data files. e.g.

Company XXX has a file named FXXX.AAA

Company YYY " FYYY.AAA and so on ....

All data for all companies has to be imported in one sql server tabel. Ofcourse with a company field.

- ODBC Does not allow sql command from variable

Errors
[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Is there anyone who had the same problems ?

By the way the sql command from variable is double checked a thousand times :)

Thanks

Ronny

0x80040E00 is not an SSIS error code. Instead it is most likely coming from the provider so you should see if the AS400 OLEDB provider you are using lists this error and what it means.

Matt

|||

Hello Matt,

This could be , but why was it then working under Sql server 2000 ? Also the ODBC connections in DTS200 were far more ?ntelligent".

regards

Ronny

oledb / odbc connection timeout

Hi - does anyone know of a sql server - server side resource limit that will
timeout an oledb connection running a simple select statement ? I am only
aware of the client side timeout options are there any server side limits
that will do this ? A user of mine is experiencing a timeout and I cant
reproduce the error and I just want to make sure I am not missing something.
Many thanks.
Ian
Timeouts are handled through the connection (client-side). What's the exact
error message (there are a couple different kinds of timeouts) and do you
have more specific circumstances? For instance, many queries time out in
EM, but run perfectly fine in QA.
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian
|||Remote query timeout is the only server-side setting which controls the
timeouts, but it only affects remote queries (i.e. server to server).
The following articles will help you get started troubleshooting query
timeouts.
http://support.microsoft.com/default...b;en-us;224453
http://support.microsoft.com/default...b;en-us;319892
http://support.microsoft.com/default...b;en-us;137983
http://support.microsoft.com/default...b;en-us;827422
Adrian
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian
|||Hi Michael
Thanks for the reply - the error message is below - but I have a growing
suspicion that my user may have connected to our server via his own sql
server (poss as a linked server). The error may then be coming from his own
server - I think I need to investigate exactly what the user is doing - I am
fairly confident now that I havent missed any obscure server side timeout.
Many thanks. If the error does give you any other idea's though - please let
me know. Best Wishes - Ian
Server: Msg 7399, Level 16, State 1, Line 12
OLE DB provider 'MSDASQL' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver]Timeout expired]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because a resource limit
was reached.].
"Michael C#" wrote:

> Timeouts are handled through the connection (client-side). What's the exact
> error message (there are a couple different kinds of timeouts) and do you
> have more specific circumstances? For instance, many queries time out in
> EM, but run perfectly fine in QA.
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
>
>
|||Hi Adrian
Many thanks for your reply - I will read the KB articles but your info
confirmed my understanding and I am now fairly confident I havent missed any
obscure server side timeout limits. I replied to Michael C# with the error
message and what I think may be happening (pls let me know if this gives you
any other ideas). Thanks again for your help - much appreciated.
Best Wishes - Ian
"Adrian Zajkeskovic" wrote:

> Remote query timeout is the only server-side setting which controls the
> timeouts, but it only affects remote queries (i.e. server to server).
> The following articles will help you get started troubleshooting query
> timeouts.
> http://support.microsoft.com/default...b;en-us;224453
> http://support.microsoft.com/default...b;en-us;319892
> http://support.microsoft.com/default...b;en-us;137983
> http://support.microsoft.com/default...b;en-us;827422
> Adrian
>
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
>
>

oledb / odbc connection timeout

Hi - does anyone know of a sql server - server side resource limit that will
timeout an oledb connection running a simple select statement ? I am only
aware of the client side timeout options are there any server side limits
that will do this ? A user of mine is experiencing a timeout and I cant
reproduce the error and I just want to make sure I am not missing something.
Many thanks.
IanTimeouts are handled through the connection (client-side). What's the exact
error message (there are a couple different kinds of timeouts) and do you
have more specific circumstances? For instance, many queries time out in
EM, but run perfectly fine in QA.
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian|||Remote query timeout is the only server-side setting which controls the
timeouts, but it only affects remote queries (i.e. server to server).
The following articles will help you get started troubleshooting query
timeouts.
http://support.microsoft.com/defaul...kb;en-us;224453
http://support.microsoft.com/defaul...kb;en-us;319892
http://support.microsoft.com/defaul...kb;en-us;137983
http://support.microsoft.com/defaul...kb;en-us;827422
Adrian
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian|||Hi Michael
Thanks for the reply - the error message is below - but I have a growing
suspicion that my user may have connected to our server via his own sql
server (poss as a linked server). The error may then be coming from his own
server - I think I need to investigate exactly what the user is doing - I am
fairly confident now that I havent missed any obscure server side timeout.
Many thanks. If the error does give you any other idea's though - please let
me know. Best Wishes - Ian
Server: Msg 7399, Level 16, State 1, Line 12
OLE DB provider 'MSDASQL' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver]Timeout expired]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute retu
rned
0x80040e31: Execution terminated by the provider because a resource limit
was reached.].
"Michael C#" wrote:

> Timeouts are handled through the connection (client-side). What's the exa
ct
> error message (there are a couple different kinds of timeouts) and do you
> have more specific circumstances? For instance, many queries time out in
> EM, but run perfectly fine in QA.
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
>
>|||Hi Adrian
Many thanks for your reply - I will read the KB articles but your info
confirmed my understanding and I am now fairly confident I havent missed any
obscure server side timeout limits. I replied to Michael C# with the error
message and what I think may be happening (pls let me know if this gives you
any other ideas). Thanks again for your help - much appreciated.
Best Wishes - Ian
"Adrian Zajkeskovic" wrote:

> Remote query timeout is the only server-side setting which controls the
> timeouts, but it only affects remote queries (i.e. server to server).
> The following articles will help you get started troubleshooting query
> timeouts.
> http://support.microsoft.com/defaul...kb;en-us;224453
> http://support.microsoft.com/defaul...kb;en-us;319892
> http://support.microsoft.com/defaul...kb;en-us;137983
> http://support.microsoft.com/defaul...kb;en-us;827422
> Adrian
>
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
>
>

oledb / odbc connection timeout

Hi - does anyone know of a sql server - server side resource limit that will
timeout an oledb connection running a simple select statement ? I am only
aware of the client side timeout options are there any server side limits
that will do this ? A user of mine is experiencing a timeout and I cant
reproduce the error and I just want to make sure I am not missing something.
Many thanks.
IanTimeouts are handled through the connection (client-side). What's the exact
error message (there are a couple different kinds of timeouts) and do you
have more specific circumstances? For instance, many queries time out in
EM, but run perfectly fine in QA.
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian|||Remote query timeout is the only server-side setting which controls the
timeouts, but it only affects remote queries (i.e. server to server).
The following articles will help you get started troubleshooting query
timeouts.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;319892
http://support.microsoft.com/default.aspx?scid=kb;en-us;137983
http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
Adrian
"Ian G" <Ian G@.discussions.microsoft.com> wrote in message
news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> Hi - does anyone know of a sql server - server side resource limit that
> will
> timeout an oledb connection running a simple select statement ? I am only
> aware of the client side timeout options are there any server side limits
> that will do this ? A user of mine is experiencing a timeout and I cant
> reproduce the error and I just want to make sure I am not missing
> something.
> Many thanks.
> Ian|||Hi Michael
Thanks for the reply - the error message is below - but I have a growing
suspicion that my user may have connected to our server via his own sql
server (poss as a linked server). The error may then be coming from his own
server - I think I need to investigate exactly what the user is doing - I am
fairly confident now that I havent missed any obscure server side timeout.
Many thanks. If the error does give you any other idea's though - please let
me know. Best Wishes - Ian
Server: Msg 7399, Level 16, State 1, Line 12
OLE DB provider 'MSDASQL' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver]Timeout expired]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because a resource limit
was reached.].
"Michael C#" wrote:
> Timeouts are handled through the connection (client-side). What's the exact
> error message (there are a couple different kinds of timeouts) and do you
> have more specific circumstances? For instance, many queries time out in
> EM, but run perfectly fine in QA.
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> > Hi - does anyone know of a sql server - server side resource limit that
> > will
> > timeout an oledb connection running a simple select statement ? I am only
> > aware of the client side timeout options are there any server side limits
> > that will do this ? A user of mine is experiencing a timeout and I cant
> > reproduce the error and I just want to make sure I am not missing
> > something.
> > Many thanks.
> >
> > Ian
>
>|||Hi Adrian
Many thanks for your reply - I will read the KB articles but your info
confirmed my understanding and I am now fairly confident I havent missed any
obscure server side timeout limits. I replied to Michael C# with the error
message and what I think may be happening (pls let me know if this gives you
any other ideas). Thanks again for your help - much appreciated.
Best Wishes - Ian
"Adrian Zajkeskovic" wrote:
> Remote query timeout is the only server-side setting which controls the
> timeouts, but it only affects remote queries (i.e. server to server).
> The following articles will help you get started troubleshooting query
> timeouts.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
> http://support.microsoft.com/default.aspx?scid=kb;en-us;319892
> http://support.microsoft.com/default.aspx?scid=kb;en-us;137983
> http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
> Adrian
>
> "Ian G" <Ian G@.discussions.microsoft.com> wrote in message
> news:CBC74343-06BC-4AC1-B68A-04E701CDB3CC@.microsoft.com...
> > Hi - does anyone know of a sql server - server side resource limit that
> > will
> > timeout an oledb connection running a simple select statement ? I am only
> > aware of the client side timeout options are there any server side limits
> > that will do this ? A user of mine is experiencing a timeout and I cant
> > reproduce the error and I just want to make sure I am not missing
> > something.
> > Many thanks.
> >
> > Ian
>
>

OLE error code:80040E14

Hi,

I am getting the following error:

OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

when trying to execute the following query:

select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id

The above query works fine on MySQL, but chokes on SQL Server.

Could anyone please help?

Thanks!

NM(neutralm@.gmail.com) writes:

Quote:

Originally Posted by

I am getting the following error:
>
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
>
>
when trying to execute the following query:
>
select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id
>
>
The above query works fine on MySQL, but chokes on SQL Server.

SQL Server, like most DB engines, as well as ANSI SQL, that if your
SELECT list includes an aggregate such as COUNT(*), and there is no
OVER clause for the aggregate, then all unaggregated columns in the
SELECT list must appear in the GROUP BY list.

Change tag_id in the GROUP BY clause to tags.id or vice versa.

Apparently MySQL is lax on this point. As a matter of fact SQL Server
4.x also permitted columns to appear in the SELECT list, if they did
not appear in GROUP BY. Sometimes the result made sense, as here
where tags.id is one-to-one with tags_id. Sometimes you got screenfulls
of garbage when you expected two lines, because you had left out a
column in the GROUP BY clause. The feature was removed in SQL Server
6.0 (and Sybase System 10), missed by few.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your prompt reply, Erland. Pardon my ignorance, but I'm
still not sure if I understood how to solve the problem (although I
think I understand what the problem is from your explanation).

I have two tables:

1. tags (with the primary key 'id' and an attribute 'name')
2. taggings (the primary key is 'id', the foreign key is 'tag_id')

The query string I'm using is:

select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id

How should the correct query look like?

Thanks so much in advance!

Erland Sommarskog wrote:

Quote:

Originally Posted by

(neutralm@.gmail.com) writes:

Quote:

Originally Posted by

I am getting the following error:

OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Column 'tags.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

when trying to execute the following query:

select tags.id, name, count(*) as count from taggings, tags where
tags.id = tag_id group by tag_id

The above query works fine on MySQL, but chokes on SQL Server.

>
SQL Server, like most DB engines, as well as ANSI SQL, that if your
SELECT list includes an aggregate such as COUNT(*), and there is no
OVER clause for the aggregate, then all unaggregated columns in the
SELECT list must appear in the GROUP BY list.
>
Change tag_id in the GROUP BY clause to tags.id or vice versa.
>
Apparently MySQL is lax on this point. As a matter of fact SQL Server
4.x also permitted columns to appear in the SELECT list, if they did
not appear in GROUP BY. Sometimes the result made sense, as here
where tags.id is one-to-one with tags_id. Sometimes you got screenfulls
of garbage when you expected two lines, because you had left out a
column in the GROUP BY clause. The feature was removed in SQL Server
6.0 (and Sybase System 10), missed by few.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||neutralm@.gmail.com wrote:

Quote:

Originally Posted by

The query string I'm using is:
>
select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id
>
How should the correct query look like?

select taggings.tag_id, name, count(*) as tag_id_count
from taggins join tags on taggings.tag_id = tags.id
group by taggings.tag_id, name

Explanations:

1) GROUP BY must include all unaggregated columns from the SELECT,
i.e. everything that is not a COUNT(), SUM(), etc. (Why doesn't
it implicitly assume this? Apparently, it used to let you leave
things out, but that caused more trouble than it was worth. The
short answer is "just give it what it wants".)

2) tags.id and taggings.tag_id are forced to be equal, so you only need
to include one of them. Optional but recommended, as it's simpler
and conserves bandwidth.

3) The join is changed from SELECT ... FROM A, B WHERE A.X = B.Y
to SELECT ... FROM A JOIN B ON A.X = B.Y
Optional but recommended, as it keeps join conditions separate from
each other, and from other restrictions (e.g. NAME LIKE '%ABC%'),
all of which makes the query easier to understand.|||Thank you very much, Ed. I really appreciate how quickly you've help me
fix this problem!

Ed Murphy wrote:

Quote:

Originally Posted by

neutralm@.gmail.com wrote:
>

Quote:

Originally Posted by

The query string I'm using is:

select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id

How should the correct query look like?

>
select taggings.tag_id, name, count(*) as tag_id_count
from taggins join tags on taggings.tag_id = tags.id
group by taggings.tag_id, name
>
Explanations:
>
1) GROUP BY must include all unaggregated columns from the SELECT,
i.e. everything that is not a COUNT(), SUM(), etc. (Why doesn't
it implicitly assume this? Apparently, it used to let you leave
things out, but that caused more trouble than it was worth. The
short answer is "just give it what it wants".)
>
2) tags.id and taggings.tag_id are forced to be equal, so you only need
to include one of them. Optional but recommended, as it's simpler
and conserves bandwidth.
>
3) The join is changed from SELECT ... FROM A, B WHERE A.X = B.Y
to SELECT ... FROM A JOIN B ON A.X = B.Y
Optional but recommended, as it keeps join conditions separate from
each other, and from other restrictions (e.g. NAME LIKE '%ABC%'),
all of which makes the query easier to understand.

OLE DB2 Provider

Hi

Running SQL 2005 standard edition, using OLE DB2 provider from Host integration server. When connected we can acces data with select statements, but cannot browse tables from import wizard in SSIS.

Is there a hint or solution?

rgds

It's probably the provider. The Enterprise Edition of Sql Server comes with a DB2 provider. You could also use an ODBC connection instead and see if that works. Finally, IBM might have a provider as well?|||Do you get an error message? I'm using the MS OLE DB2 provider quite regularly and have no issues... Setup is key, though, to getting it to work correctly.