Showing posts with label brief. Show all posts
Showing posts with label brief. Show all posts

Monday, March 19, 2012

another sql query

Hi All,

I'm really struggling to figure out and sql query that will retrieve the first 30 characters of a database field. My plan is to have a brief preview of the text and then have ...[more] afterwards. So far I have the below (text is the field I'm trying to only retrieve the first 30 characters):-

SELECT id, title, text FROM w_pages WHERE ID <> '1' ORDER BY ID

I've set the query to not retrieve id 1 as that's the text of my homepage. I've attempted LEFT(text,30) as I thought that was the way to do it, but it didn't work for me :(

Also, can these types of queries be used with sql2000 'ntext' database fields?

Many thanks in advance.

Regards,

RichI imagine the ntext is your problem. From Books online:
LEFT ( character_expression , integer_expression )

Arguments
character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that can be implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive whole number. If integer_expression is negative, a null string is returned.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]))

Giving the EXACT error message you are getting would really help quite a bit.|||Thanks for the speedy reply.

I'm still new to asp.net and sql queries and I appreciate all your help :)

I've tried what you suggested and now it's giving the below error message:-

-------

Server Error in '/' Application.
------------------------

The left function requires 2 arguments.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The left function requires 2 arguments.

Source Error:

Line 121: Dim resultsDataSet as New DataSet()
Line 122: Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(sql, myConnection)
Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124: Content.DataSource = resultsDataSet
Line 125: Content.DataBind()

Source File: E:\kunden\homepages\1\articles.aspx Line: 123

Stack Trace:

[SqlException: The left function requires 2 arguments.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.articles_aspx.GetContent() in E:\kunden\homepages\1\articles.aspx:123
ASP.articles_aspx.BindData() in E:\kunden\homepages\1\articles.aspx:107
ASP.articles_aspx.Page_Load(Object Sender, EventArgs E) in E:\kunden\homepages\1\articles.aspx:13
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +742

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.910

--------

Regards,

Rich|||OK. Sorry about that.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]),30)|||Thanks for that, a different error now (below) which is confusing, as I know the database field exists.

Sorry to keep bugging you with this problem.

Regards,

Rich

-----------------------------

Server Error in '/' Application.
------------------------

A field or property with the name 'text' was not found on the selected datasource.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: A field or property with the name 'text' was not found on the selected datasource.

Source Error:

Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124:Content.DataSource = resultsDataSet
Line 125: Content.DataBind()
Line 126:End Sub
Line 127:|||Then, perhaps the column name in the table is not text?|||Very strange, I just checked the column name and it's definately 'text'.

Oh well, I'll have a play about with it and see what happens.

Thanks for all your help, it's much appreciated.

Regards,

Rich|||Got it working :)

I used:-

SELECT id, pagedate, title, show, LEFT(CONVERT(nvarchar(300),[text]),300) as '111' FROM w_pages WHERE id <> '1' AND show = 'yes' ORDER BY ID DESC;

And then changed the datafield in my datagrid to '111' instead of 'text'

Thanks again for your help :)

Regards,

Rich

Thursday, March 8, 2012

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrb
I suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
David Portas
SQL Server MVP
|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --
|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)
|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
David Portas
SQL Server MVP

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrbI suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
--
David Portas
SQL Server MVP
--|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
--
David Portas
SQL Server MVP
--

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrbI suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
David Portas
SQL Server MVP
--|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
David Portas
SQL Server MVP
--