Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)

Distributor cannot connect to subscriber

I am setting up my 2005 Replication system...

publisher = 2005 sp1

Subscriber = 2005 sp1

I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.

Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.

I am not sure where to look at next... I never had this issue in 2000.

Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.

I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.

Could it be that my pub/dist is using the wrong client files?

|||

Hi William,

Are you using merge or transactional replication?

Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.

In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?

Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.

This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hey Tom,

I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.

The subscription is a push subscription. I will take a look at the link you sent.

=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.

Is there anything I can do to ensure it is working?

|||

update

Since my subscriber is a dev server and if I mess it up it does not matter I did the following...

I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.

No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...

Any idea? or is this a Bug?

|||

Can you try the following connectivity test from command prompt?

at your dev machine, try to connect to the other box

osql -S<publisher_server> -U<user id> -P<password>

and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.

Gary

|||

Ok... I did as suggested and the publisher has no problem connecting to the subscriber...

So what does that mean?

I can connect via command line,SMS but not replication.

|||

Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....

So when I execute from 80/tools/binn I can connect without issue.

From 90/tools/binn I get this error

[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.

|||

Can you check the protocal interface are enabled for TCP and name pipe?

You can find the instruction on the following posting from Mahesh,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1

Gary

|||

I double checked and I already have that set. I cycled the server anyway... Still no luck.

What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...

Is there anyway to check what libraries each client is using?

|||

What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

OS is Windows 2003 SP1...

I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...

I

|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||

That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...

So I am asking my boss for other options...

Thanks for all the help.

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?