Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

Query Output Custom Delimiter

Hello,

I have a SSMS query result set that includes columns which contain commas. I used the save as feature in the Results pane in SSMS but saves it as a comma delimited file. I tried to change the delimiter using in Query Options/Custom Delmiter to semi colon however it still saves it with a comma delimiter.

Any suggestions on how to change the delimiter?

Thanks

Are you using the save to file option for the query or are you selecting all the data and then saving it?

I ran this test, and I got the commas into a single column. The difference is that I made the file type *.*, and used a .TXT extension.

CREATE DATABASE TEST;

GO

USE TEST;

GO

CREATE TABLE TestComma (column1 varchar(30));

GO

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test')

INSERT INTO TestComma

VALUES ('This is a comma, test');

GO

-- Now run this to save output into text file:

SELECT * FROM TestComma;

GO

-- Use the Save as feature:

SELECT * FROM TestComma;

GO

|||I selected all data in the query results window then saving it. The only option is to save it as a .csv file and I was wondering if that can be changed.|||

As I mentioned, use the *.* file type, and then specify a name. Then it's just text and not formatted as a CSV.

Query on Custom source Component

Hi

in the acquireconnection method Using the below statment I can get a connection Object

oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

from the connection object I can get the connectionstring from the object by calling

oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.

How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.

any help is much appriciated

thanks in advance.

Just a guess, but look up OleDbConnection in the framework documentation, and see this-

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.

Saying that, the password property may just be write-only, they often are for security, but I cannot find documentation for the extended properties right now.

|||

so does that mean that its not possible to extract password from connection Object

|||

Hi,

we are in process of developing custom source component. At design time we are using OLEDB connection to get the metadata.

we want to use the connection information given at design time by user to initialize OracleConnection of System.Data.OracleClient.

Can anybody guide us how to acheive this?

Thanks,

Anil

|||

To be clear you must be using an ADO.Net OLE-DB connection, not the native OLE-DB connection otherwise you won't be able to make head nor tail of the acquired connection. With that in mind, why not just use an ADO.NET OracleClient directly and stop messing about with "converting" it?

|||

Hi Darren,

Thanks for your reply. but we cant go ahead by specifying the connection type as ADO.Net: OracleClient as we are using the UI for some other component as well.

Is there any other way to do the same?

Thanks,

Anil.

|||Why do you want OleDb connections one minute and Oracle the next. They are very similar, but yet still different so masking one with the other does not make sense. Having multiple connections is not a problem. What UI? If it is your own Ui creating the OleDb connection, then why can it not create Oracle as well?|||

I created a single connection, ConnectionManagerType - ADO.NET:System.Data.OleDb.OleDbConnection..., which used the "Microsoft OLE DB Provider for Oracle" underneath.

I then used the following Script Task code to display the connection string and it shows the password just fine.

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections(0)

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

Dts.TaskResult = Dts.Results.Success

End Sub

I cannot see the problem, password is there, is this not what you want?

|||

Hi Darren

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

If the User Selects the Check Box "Save Password" then the password is there in the connection string Otherwise no.

Is there any way that we can get the password if the user has not select the option to save the password.

|||

No.

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.