Clear connection history

Hello
Does anybody know how to clear the logon/connection history in SQL
Management Studio 2005.
Many thanks
DarrenDo you mean in the SQL Server Logs? You can use sp_cycle_errorlog to cycle
the log into Archive logs...
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Darren.Ratcliffe@.gmail.com" wrote:
> Hello
> Does anybody know how to clear the logon/connection history in SQL
> Management Studio 2005.
> Many thanks
> Darren
>

Clear connection history

Hello
Does anybody know how to clear the logon/connection history in SQL
Management Studio 2005.
Many thanks
Darren
Do you mean in the SQL Server Logs? You can use sp_cycle_errorlog to cycle
the log into Archive logs...
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Darren.Ratcliffe@.gmail.com" wrote:

> Hello
> Does anybody know how to clear the logon/connection history in SQL
> Management Studio 2005.
> Many thanks
> Darren
>

Clear connection history

Hello
Does anybody know how to clear the logon/connection history in SQL
Management Studio 2005.
Many thanks
DarrenDo you mean in the SQL Server Logs? You can use sp_cycle_errorlog to cycle
the log into Archive logs...
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Darren.Ratcliffe@.gmail.com" wrote:

> Hello
> Does anybody know how to clear the logon/connection history in SQL
> Management Studio 2005.
> Many thanks
> Darren
>

Citrix Connection to Enterprise Manager

I am trying to set up a citrix connection, so that enterprise manager can be
opened from an ICA client. Is this even possible. And if it is, which file do
I point the app to to run it. I have already tried the msc file, and no luck
Hi
In your Citrix application, you enter the command line like this -
M:\WINDOWS\system32\mmc.exe /s "M:\Program Files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
Regards
Steen
DBA wrote:
> I am trying to set up a citrix connection, so that enterprise manager
> can be opened from an ICA client. Is this even possible. And if it
> is, which file do I point the app to to run it. I have already tried
> the msc file, and no luck

Citrix Connection to Enterprise Manager

I am trying to set up a citrix connection, so that enterprise manager can be
opened from an ICA client. Is this even possible. And if it is, which file d
o
I point the app to to run it. I have already tried the msc file, and no luckHi
In your Citrix application, you enter the command line like this -
M:\WINDOWS\system32\mmc.exe /s "M:\Program Files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
Regards
Steen
DBA wrote:
> I am trying to set up a citrix connection, so that enterprise manager
> can be opened from an ICA client. Is this even possible. And if it
> is, which file do I point the app to to run it. I have already tried
> the msc file, and no luck

Citrix Connection to Enterprise Manager

I am trying to set up a citrix connection, so that enterprise manager can be
opened from an ICA client. Is this even possible. And if it is, which file do
I point the app to to run it. I have already tried the msc file, and no luckHi
In your Citrix application, you enter the command line like this -
M:\WINDOWS\system32\mmc.exe /s "M:\Program Files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
Regards
Steen
DBA wrote:
> I am trying to set up a citrix connection, so that enterprise manager
> can be opened from an ICA client. Is this even possible. And if it
> is, which file do I point the app to to run it. I have already tried
> the msc file, and no lucksqlsql

Choose Provider (SQL or Oracle) at Deployment Time

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?

Thanks,

Rick

Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though. (Oracle numeric data types come to mind as a problem mapping to SQL Server)

You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.|||

RickGaribay.NET wrote:

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?

Thanks,

Rick

Yes, this is possible. The provider is within the ConenctionString property which can be changed at execution-time using configurations or property expressions.

More on property expressions: http://blogs.conchango.com/jamiethomson/archive/tags/Expressions/default.aspx

-Jamie

|||

Phil Brammer wrote:

Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though.

Probbaly only if you are using data-flows - which need not be the case.

Phil Brammer wrote:

You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.

This is a good idea. You can make the connection string property conditional as well using property expressions (see my earlier post).

-Jamie

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Checklist to TroubleShoot SQl Server 2005 Connection Problem

I am trying to connect a production SQL Server 2005 which is under firewall through an Application written in ASP.Net 1.1 and I get error "SQL Server not available or Access Denied".

I am able to connect to the Server through SQL Mgr also using the same Code and Web.Config file I am able to Connect to the SErver using ASP.Net 2.0.

I am able to run the application for a SQL Server 05 which is in the same domain as of the Application Server.

My Application is not working only when I use ASP.Net 1.1 code for the Remote SQL Server 05 which is under Firewall. But for the same Server ASP.Net 2.0 works fine.

I have searched a lot for the problem and port No, DBNETLIB update is also tried.

Please let me know if anyone has came across such problem or knows the possible cause of the problem.

Sunil.

Can you post your connection string for both ASP.NET 2.0 and ASP.NET 1.1 app?|||

Sure, I am using the same connection String for both versions.

In Web.config I have added the following Connection String :

<add key="CnString" value="data source=<IP of Server>;User Id=sa;password=sa;Initial Catalog =<Database>"/>

Code from aspx file :

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["CnString"]);

conn.Open();

Response.Write("Connection Opened");

conn.Close();

When the same code is written in ASP.Net 2.0 it gets executed but through Asp.Net 1.1 its throwing exception as "Sql Server does not exist or Access Denied"

|||

Hi Sunil,

Is the ASP.Net 2.0 and ASP.Net 1.1 both run on the same machine? Is there more error message text available? Also, the obvious question is are you using the same IP address in both applications?

Another thing you can try is to force the app to TCP and see what happens. You can do this by prefixing the server name with "tcp:". FYI, .Net 2.0 will always try TCP first for remote connections while this isn't always the case for 1.1.

Il-Sung.

|||

Hi, Thanks for the "tcp" suggestion. I will check whether it works or not.

I have two applications both having the same code which I have posted above. One is developed in VS2003 and other in VS2005.

Both these applications are using same connection string and talking to same SQL 2005 server.

Also earlier we were using SQL 2000 server which was also under Firewall but it use to work with the above mentioned Connection String very well.

About error message dont have details but error Source is System.Data.SqlClient.

I will try the "tcp" option and will get back soon.

Thanks.

Sunil.

|||

Hello,

Our problem is solved. It happened because the user who shifted Databse from Sql 2000 to Sql 2005 was not DBA so direct procedure names were not working for the Database in 2k5. We gave DBO rights to the user and problem was solved.

Thanks to all of you for your time and suggestions.

Sunil.

|||

Following is the checklist we have prepared to be considered if anyone came across Connection problem with Sql Server 2005.

1) Is the Database is properly shifted from SQL 2000 to SQL 2005.

While shifting the Database from SQL 2000 to SQL 2005, strictly Administrator login tote used i.e. using "sa" login only. If this is not done then the schema

definition of the underlying Tables and Procedure changes from [dbo].Tablename to [LoggedInUserName].TableName and which will give error while using the

Database Objects.

2) Get confirmed from Client whether SQL Server is in same Domain as of Application Server or Not

If Application Domain and Sql Server Domain are different in that case there are Few things to be checked/enabled on SQL Server

Check whether Server is working on Windows Authentication / Sql Authentication

Is remote access allowed for Sql Server

Is the default ports 1433/1434 are opened

Is protocols TCP/IP and Named Pipes Enabled under Sql Configuration --> Protocols

Is Sql Browser service is running

Is proper permissions are granted for the Database to be used, for the user used in connection string ( if Sql Authentication is used )

If Windows Authentication is used the ASPNETUSER must be added to Logins in SQL Server Logins and must be given proper permissions to access the Database.

Checking The Users Server Role

I would like to determine if a particular user has sysadmin server
role. Is there a way to do this via the connection string? Currently
our code checks if a login is valid using SQLDriverConnect, however we
need to be certain that the user can login and modify the schema.

Is it possible to fetch a user's server role to determine if it has a
sysadmin server role?Look for the IS_SRVROLEMEMBER function in Books Online.

Razvan|||Thanks.

Checking if DB Connection is active or not

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata NarayanaIn the above one, please read it as SELECT 1 instead of SELECT * from 1

Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.
>
This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)
>
My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.
>
a) Create Table
b) Use SELECT query
c) Drop table
>
You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.
>
One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.
>
So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?
>
Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.
>
Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,
>
Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});
>
The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.
>
What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:
>
try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}
>
This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).
>
Any comments on this would be appreciated.
>
Regards,
Venkata Narayana

|||Venkata Narayana wrote:

Quote:

Originally Posted by

In the above one, please read it as SELECT 1 instead of SELECT * from 1
>
Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

Whatever you do to test a connection, you want it fast and non-taxing
of DBMS resources, so even if you have table-create permissions, you
don't want to do that. The fast thing is a DBMS-specific query:

Sybase, MS: select 1
Oracle: begin null; end; or select 1 from dual
DB2: select 1 from sysdummy
etc.

You can always call DatabaseMetaData.getDatabaseProductVersion()
to figure out what DBMS-specific SQL to send.
If you really must be DBMS-neutral, you can call DatabaseMetaData
getTables() with arguments that define a non-existent table. The DBMS
will still have to look, but the search for a single table
'NONEXISTENT'
won't be too bad.
Lastly, note that whatever you use to test a connection, the
connection
may fail the very instant after your test succeeds, so your subsequent
code will have to be able to deal with a broken connection anyway. In
practice you would only want to test connections that had been sitting
idle for a significant period.

Joe Weinstein at BEA Systems

Check the SSIS FTP Connection String

Hi,

i have a table like

No

FileLocation

UserName

Password

1

ftp://sarvi3/test/

xyz

xyz

2

ftp://pandit3/test/test1

abc

abc

3

ftp://katta1/test/test2

klm

klm

i want to check the filelocation is valid (exist) or not before transfering the file from the location. can anyone help me out to resolve

thanks

Sun

Would any checking not be part of the actual transfer code? perhaps some sort of error trapping?

I've always found FTP to be a pain... although I'm willing to accept that could well be down to me rather than any shortcomings in FTP :)

Is this really a TSQL related question? I'd have thought you'd get more joy from one of the scripting forums.