Bug in SQLDMO SQLServer.Connect if server is behind a firewall

Apparently the Connect method (as well as the PingSQLVersion method) of the
SQLServer object in the SQLDMO library does not honor any of the timeout
property values (BlockingTimeout, LoginTimeout, or QueryTimeout) if a remote
server is firewalled.
Apparently if the remote server is firewalled, the connection attempt will
always timeout after about 45 seconds.
What is interesting is the server responds and is added to the NameList
object when an Application.ListAvailableSQLServers method call is made. I
believe this works by sending a broadcast on UDP port 1434, correct? So why
would this respond even though the port is firewalled -- that's what I find
a
bit odd, or at the very least inconsistent.
Granted, if the server is local and it's firewalled, it will connect. I'm
guessing SQLServer.Connect has a few methods in which it tries to make the
connection.
I've been able to reproduce this in a consistent manner dozens of times. I
guess my question is whether or not there's a workaround or something else I
can do to prevent my application from waiting until that timeout limit is
reached.Hi
Are you sure the firewall does not have specific rules that allows you to
connect on the port rather than a blanket block?
John
"Rob Gomes" wrote:

> Apparently the Connect method (as well as the PingSQLVersion method) of th
e
> SQLServer object in the SQLDMO library does not honor any of the timeout
> property values (BlockingTimeout, LoginTimeout, or QueryTimeout) if a remo
te
> server is firewalled.
> Apparently if the remote server is firewalled, the connection attempt will
> always timeout after about 45 seconds.
> What is interesting is the server responds and is added to the NameList
> object when an Application.ListAvailableSQLServers method call is made. I
> believe this works by sending a broadcast on UDP port 1434, correct? So w
hy
> would this respond even though the port is firewalled -- that's what I fin
d a
> bit odd, or at the very least inconsistent.
> Granted, if the server is local and it's firewalled, it will connect. I'm
> guessing SQLServer.Connect has a few methods in which it tries to make the
> connection.
> I've been able to reproduce this in a consistent manner dozens of times.
I
> guess my question is whether or not there's a workaround or something else
I
> can do to prevent my application from waiting until that timeout limit is
> reached.|||Not sure. This is the firewall provided in Windows XP.
Moreover, what I'm more concerned with is the fact that when calling the
Connect() method, instead of failing immediately (or after the LoginTimeout
expires) it times out after 45 seconds -- despite what I define for the
timeout properties for the SQLServer or SQLServer2 object.
"John Bell" wrote:
> Hi
> Are you sure the firewall does not have specific rules that allows you to
> connect on the port rather than a blanket block?
> John
> "Rob Gomes" wrote:
>|||Only LoginTimeout has any effect on this behavior, BlockingTimeout and
QueryTimeout have no affect.
Which client protocols are enabled and what is the default protocol?
If I am forcing TCP it works on my machine as expected, when the firewall is
closed it returns after 15 seconds, using the following code sample
using System;
using System.Runtime.InteropServices;
using SQLDMO;
namespace SDN
{
/// <summary>
///
/// </summary>
class App
{
[MTAThread]
static void Main(string[] args)
{
try
{
SQLServer2Class s = new SQLServer2Class();
s.LoginSecure = true;
s.LoginTimeout = 15;
s.Connect("tcp:gertd00,1433", null, null);
s.DisConnect();
}
catch(System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex);
}
catch(System.Exception ex)
{
Console.WriteLine(ex);
}
}
}
}
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Rob Gomes" <RobGomes@.discussions.microsoft.com> wrote in message
news:DD239C68-DC06-42B0-96B8-67DDEBB7EE90@.microsoft.com...
> Not sure. This is the firewall provided in Windows XP.
> Moreover, what I'm more concerned with is the fact that when calling the
> Connect() method, instead of failing immediately (or after the
> LoginTimeout
> expires) it times out after 45 seconds -- despite what I define for the
> timeout properties for the SQLServer or SQLServer2 object.
> "John Bell" wrote:
>

Bug in Flat File Connection Object: Risky!!!!

I was wondering if any others on this list are getting a bug with the Flat File connection object --where in both csv (coma delimited) or flat files (tab delimited) where strange characters(like two bold vertical lines) are appearing in the file viewer while setting up a connection forcing the CRLF to end in the wrong place. They look like two bold vertical lines

Im testing the same files with both the old DTS from SQL Server 2000 and getting no issues which tells me there is a bug in SSIS. Strange thing the bug does not happen with every file I receive, only a few. I’m wondering if SSIS Flat File Connection object could be a little flaky with respect to how it reads files compared to DTS text connection object.

email me d2ba@.xtra.co.nz for a screen shot of tests

Thanks in advance

Dave

This is due to the way the SSIS flat file parser is designed. Missing delimiters break the parsing. See the following post fo more details:

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

Thanks.

|||

Bob,

I had a read --all I can say is Risky to implement the flat file connection object this way in an ETL tool. Risky Risky Risky

The best workaround is to use DTS 2000 to import the flat files.Simply run the DTS 2000 package via SQL Server 2005 Agent using the backwards compatibily.

Once the data is in the SQL Server 2005 staging tables use SSIS for the rest of the package.

Cheers Dave

|||

I have to say I am puzzled by the term "risky" - the behaviour of the current flat file parser is entirely predictable and was introduced in part because the old DTS behaviour could introduce unexpected results - it was also much slower.

The two black characters you are seeing are probably undisplayable characters such as <CR> or <LF>. I expect they are in some lines in some files and not in others - but you might not be able to tell that in other editors as they are undisplayable. I would think it much more risky to behave as if they were not there or to make assumptions as to what was intended when a certain parsing was selected. And indeed, we have plenty of issues in our bug database and customer feedback concerned about the DTS parsing for these reasons.

That's not to say that we could not introduce more flexibility into the current design - although all the flexibility one could want is available through the script source component in 2005.

Perhaps there are some specific aspects of the older DTS parsing you would like to retain, and I would be glad to hear of those. We're always open to suggestions.

Donald

|||I also saw this. Nothing is perfect. I will prefer SSIS to DTS within six months no right now, when I've got as a dutie move hundreds of packages to SSIS, you know. Old habits...|||

Here is what I would like to see in the next release:Until then Im stuck with using DTS imported into SSIS for the dataflow task on certain file types we recieve--unless somebody can show me a script component that can to this. Sometimes I have first row as column names in file and other times I dont have column names in file.

the row delimiter used to be read before the column delimiter in the DTS world... it would be great to be able to set this as an explicitly selected option

Problem Statement:

I have a tab delimited flat file with say 60 columns. All columns can have null values. The file contains a blank tab for nulls.

Now, if a row has null value for last 10 columns, ideally, there should be 10 blank tabs padded to the end of that row. But, this is not the case with this file. There is line break after 50 columns of that row, in the above example.

Extrapolating this behavior, most of the rows have null values for last 'x' columns where 'x' is variable for each row.

When I try to customize a flat file connection for this file, SSIS is not padding up the missing tabs. In the example above, it should insert null in the last 10 columns for that row. Instead, and weirdly, it is continuing to read next row. So, column 1 of next row is inserted into 51 st columns of the above row.

Even Excel 2000 can handle this same file easily with the same delimiters (row - LF and column - tab).

Reason Why SSIS behaves this way:

The flat file parser parses data per-column using current column delimiters, and then advancing to the next one. The row delimiter is considered as nothing more then a column delimiter of the last column.

We considered adding what you are asking for; always check if we get the row delimiter in addition to checking for the current column delimiter, and if the row delimiter comes before expected column delimiter pad the remaining columns of that row.

We decided not to implement this by default for performance reasons. It is likely we will have it in the next version of the product, but only as an option that will need to be explicitly selected.

|||

I'm sorry I don't have time to craft a full script sample for you - perhaps someone out there in the community may be able to.

Is there a specific issue with writing the script that is causing problems? I may be able to advise.

Meanwhile - we'll continue to consider alternative parsing methods for future releases.

Donald

|||

Dave,

I am not an advanced user of SSIS at all BUT things that used to be simple in DTS are often more convoluted than I'd expect in SSIS. Indeed, I ran into various problems with their Flat File Connection object but I presumed that it was my lack of understanding of how to use it. They just seem to have re-architected the way some familiar things worked and have not done a very good job of telling people how they have changed.

Barkingdog.

BPA configuring 'Object Prefix'

I would like to configure the prefixes for several objects but I cannot figure out how to do so. From what I saw in the Properties section, it will only allow 1 property (I have already set up Stored Procedures) but I would like to do several (Stored Pro
cedure, Functions, Views, etc.) Can anyone point me in the right direction?
Thanks,
Jeff
Hi Jeff
Yes, you can only configure one rule per bet practice group. In order to
accommodate multiple checks, you'll have to create several separate Best
Practice Groups. We'll look into how to provide what you want in a single
best practice group in a future release.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff Kararo" <JeffKararo@.discussions.microsoft.com> wrote in message
news:9A3FBB14-CC30-4691-B4A3-5E3BD29633B3@.microsoft.com...
> I would like to configure the prefixes for several objects but I cannot
figure out how to do so. From what I saw in the Properties section, it will
only allow 1 property (I have already set up Stored Procedures) but I would
like to do several (Stored Procedure, Functions, Views, etc.) Can anyone
point me in the right direction?
> Thanks,
> Jeff
>