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 DELETE sql statement to delete from two tables

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

I think Triggers may help for this scenario...

I am not sure about one delete statement to delete from two tables.

Thanks

Sreekanth

|||

If both tables has the relation (child-master with foreign key), then you can use the ON DELETE CASCADE on your Primary Key (Master) constraint.

Code Snippet

Create MasterTable

(

Id int Primary Key On Delete Cascade,

..

..

)

Create Childtable

(

Master_TableId int References MasterTable(Id)

..

..

..

)

If the tables doesn’t have any relation (logically bound), then you can use the Trigger to delete the values from the tables – using DELETED special table.

Code Snippet

Create trigger trigger_name On FirstTable For Delete

As

Begin

Delete From SecondTable Where ID in (Select ID from DELETED)

End;

Go

Delete From FirstTable

If you use SQL Server 2005, then you can use the OUTPUT clause, to get the deleted values from the current table, and supply those deleted values to remove the records from other table.

Code Snippet

Declare @.DeletedIds Table

(

Id int

)

Delete FirstTable OUTPUT DELETED.ID INTO @.DeletedIds Where <condition>;

Delete From SecondTable Where ID in (Select Id from @.DeletedIds);

|||

More information may be required for answering this. The simple answer is, no, you cannot "technically" do this. Any method that you can simulate this will technically be multiple SQL operations. Triggers, ON CASCADE constraints, etc.

There is really no need for one statement to delete from multiple tables, the key for your needs is probably that you want to make sure that if one statement completes, then the other statement completes. This is referred to as an atomic operation, and is managed by Transactions. So, simplistically (you need error handling/messaging for sure) in the following:

BEGIN TRY

BEGIN TRANSACTION

DELETE 1

DELETE 2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

If DELETE 2 fails, but DELETE 1 succeeds, DELETE 1 will be undone.

sql

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

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 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 command gets compile errors but works in Query analyzer.

The following statement is valid in query analyzer but will not compile as a prepared statement in an OLEDB command in DTS 2005.

delete
from
purEncumbrance_Fct
where
AgreementId = ?
and FundId = ?
and AccountId = ?
and coalesce(PODistributionId,0) = coalesce(?,0)
and coalesce(VoucherDistributionId,0) = coalesce(?,0)

Why does this statement not compile?

KenTry putting square brackets around the table and column names. I have a vague recollection of this working for me in the dim and distant past.

-Jamie|||Jamie,

I tried your suggestion with great hopes, but it did not work.

I wonder if this is a bug or a limitation with prepared statements. I know this command works in query analyzer, so maybe if I place it in a stored procedure it will work. I don't want to have to manage another piece of code, but if that is what it takes, I will.|||Not tested, but I know the statement prepare stuff and OLE-DB parameters can be rather fussy. Try loosing the coalesce(?, 0) and just use ?. Assuming that works, handle the coalesce values through a derived column, e.g.

ISNULL(Column) ? 0 : Column

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/DB provider returned message: Deferred prepare could not be completed

I have 2 SQL servers. And in the first one I have added the second SQL as a Link Server. When I run an SQL statement on the linked server I get the following message.

Server: Msg 7202, Level 11, State 1, Line 1
Could not find server 'PROD' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

The SQL statement that I am runnins is

Select * from openquery(PROD,'Select * from PROD.GMS.dbo.qryDispCL')

But when I run only the SQL statement "Select * from PROD.GMS.dbo.qryDispCL" it works perfect. But I need to have the first statement running.

Please help. Your valuable feedback is greatly appriciated.

It's really strange... You can run this to check registered server:

EXEC sp_helpservers

If the server 'PROD' is not in the result, then run this to add it:

EXEC sp_addlinkedserver 'PROD'

Note: you may need to add login information for the 'PROD' server with 'sp_addlinkedsrvlogin'.