Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable
way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?mike,
From a security point of view, and probably also performance, two
connections from the web server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferab
le way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?

Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?
mike,
From a security point of view, and probably also performance, two
connections from the web server.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?
sql

Preferable way to use two servers

I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
- Make two connection objects and connect to both servers. or
- Fetch data throug one connection by means of a linked server?mike,
From a security point of view, and probably also performance, two
connections from the web server.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
mike wrote:
> I use data from two SQL servers to make up a webpage. What is the preferable way to fetch the data.
> - Make two connection objects and connect to both servers. or
> - Fetch data throug one connection by means of a linked server?

Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

Pre-execute Hangs at 50%

Hello,

I did a search and found no answers. I have a simple project; one data flow task reading from one DB2 tabel and writing to another. My OLEDB source is a sql command that returns 97,000 rows and my OLEDB destination's data access mode is "table or view".

For my OLEDB source SQL command, I run it through the "build query" panel and I get all 97,000 rows back within 90 seconds everytime.

I don't know what is so different since I currently do SQL commands that bring back 2.5 million rows from DB2 with no effort in SSIS. I even took my data flow task and placed into a known good solution - but still hangs at 50% forever. I tried attaching to SSIS server and executing through there, again no luck. Still hung!

Any ideas? Thanks.

On your OLE DB Source component in the data flow, set its property, "ValidateExternalMetadata" to false and see if that helps.

One other thing to try is to set the property of the DB2 connection manager object, "DelayValidation" to true.|||

Thanks for your reply Phil.

I went ahead to set both properties accordingly and reran. After 16 minutes of running it was still on the pre-execute phase stuck at 50%.

Just to test I changed my DB2 SQL to FETCH ROW FIRST ONLY and it still hangs at the pre-execute 50% level. So obviously it's not the number of rows causing the problem.

What I don't understand is using the "preview" panel in the OLEDB source, I get the first row in about 6-7 seconds.

Thanks.

|||Do you have any other connection managers defined in the package? Validation will attempt to validate all connections.

What DB2 driver are you using?|||

No, that is the only connection I have in my package/solution.

I am using "Native OLE DB\IBM OLE DB Provider for DB2".

|||

Is there any way to see a log of where it's getting hung? Maybe its my OLEDB Desitnation...since everything is perfect with my source.

Any ideas on how to verify my idea? Thanks.

|||

I went ahead and changed my destination to a temperary SQL 2005 table and now it works fine...so It is my destination. It must be failing on something (connection/rights?) and not telling me why?|||

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

|||

Zach84 wrote:

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

Be careful... You aren't using a Microsoft driver. It isn't doing any selects or inserts -- it's just trying to validate metadata and such.

You could always try the Microsoft OLE DB for DB2 driver, which I've had better luck with.

pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql