Forcing types in Excel connection?

I am using Crystal Reports 9 to build a report that involves connections to both an Excel spreadsheet and an Oracle database. The problem is, the Oracle database contains an ID field stored as text (although the data is currently strictly numeric). The same ID field appears in the excel spreadsheet. I am trying to join the spreadsheet to the database. My problem is that, since ID field in the spreadsheet contains only numeric data, Crystal Reports imports the field as a numeric field then expects it match to the Oracle database's text ID field! Obviously, this creates a type error and the report fails.

My question is: is there any way to force Crystal Reports to regard a field in Excel as text rather than numeric data? Changing the cell attribute to text in Excel didn't seem to work; Crystal still saw numbers in the field and assumed the data was numeric.

This is an urgent matter so any help at all would be appreciated. Thanks!When joining the tables convert oracles table's id to numericsql

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

Force protocol encryption

I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?

Thanks!If using OLEDB, add this to the connection string:

Use Encryption for Data=True

For Each Container for XML Source Adapter

I know how to For Each Loop a Flat File... But there is not File connection to reference for the XML Source adapter.

How do I run XML files through my DataFlow from a directory with the For Each Loop?I am going to assume two things:
1. the schema is the same for all of the files, and you have already set up the XmlSrc using valid data and schema to get the metadata initialized
2. you have, in your for loop, a variable with the name of the current file

In your XmlSrc, in the advanced editor, Component Properties, under Custom Properties, set the AccessMode to 1 (this means the file name is in a variable), and set the XMLDataVariable property to the name of the variable with the file name.

Let me know if that works for you.
Thanks,
Mark|||Marc,

Thank you for the Late Night answer. It worked perfectly, and I would have not figured that out without you.

Your Money!