Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with One-to-Many query

Given the following two tables:
Parent Child
-- --
ParentID ParentID
ChildID
how can I retrieve the Child row which has the highest value of
ChildID within any single Parent row?
The following does not work.
SELECT * FROM Parent p
INNER JOIN Child c ON p.ParentID = c.ParentID
WHERE c.ChildID = (SELECT MAX(c.ChildID))
Thanks LarsI think you want:
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Hit send too fast...
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
GROUP BY ParentId
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||SELECT *
FROM Parent p
INNER JOIN (select child.parentId, max(child.childId) as
childId, <other columns>
from child
group by child.parentId) as c
ON p.ParentID = c.ParentID
and p.childId = c.ChildId
This will work fine if you actually want all rows from parent matched with a
child. We might have to optimize some if you only want a small percentage
of the rows in parent.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Try,
use northwind
go
select
oh.orderid,
oh.orderdate,
od.max_productid
from
orders as oh
inner join
(
select
orderid,
max(productid) as max_productid
from
[order details]
group by
orderid
) as od
on oh.orderid = od.orderid
-- or
select
oh.orderid,
oh.orderdate,
od.productid
from
orders as oh
inner join
[order details] as od
on oh.orderid = od.orderid
where
od.productid = (select max(a.productid) from [order details] as a where
a.orderid = oh.orderid)
AMB
"larzeb" wrote:

> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
>|||serveral ways to do it I guess, but you can use a sub-query for instance
SELECT
c.*
FROM
child c
INNER JOIN
(SELECT
parentid
,MAX(childID) ChildID
FROM
parent
GROUP BY
parentid) vt
ON c.childid = vt.childid
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

GO

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

GO

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

GO

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

GO

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?