Multiple Instances of Sql Server on a machine.

We are currently writing software that connects to Sql server 2000. We use a
connection string that provides the name of the server machine that
sql-server resides on. What do we do when there is more than one sql-server
on that machine. For example, there are instances A,B, and C on the machine
and we want to work with instance B. What changes do we need to make to our
connection string to make this happen? We aren't using DSN's by the way.
Thanks in advance for any assistance.Ed Hawkes wrote:
> We are currently writing software that connects to Sql server 2000.
> We use a connection string that provides the name of the server
> machine that sql-server resides on. What do we do when there is more
> than one sql-server on that machine. For example, there are instances
> A,B, and C on the machine and we want to work with instance B. What
> changes do we need to make to our connection string to make this
> happen? We aren't using DSN's by the way. Thanks in advance for any
> assistance.
Provide the instance name (SERVERNAME\INSTANCENAME)
David Gugick
Imceda Software
www.imceda.com

Multiple counts in table

I've got a table containing (among some other columns) 3 different type columns.

TABLE
xxxx as int
type 1 as string
type 2 as string
type 3 as string
date as Date

First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type.

select type1, count(type1) from table group by type1
union
select type2, count(type2) from table group by type2...

Now this is time consuming and not what I would like to do

Secondly i'll try to do something like

SELECT type1 ,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar'
,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar'
,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars'
,SUM(CASE WHEN MONTH(date) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(date) = 5 THEN 1 END) AS 'Mai'
,SUM(CASE WHEN MONTH(date) = 6 THEN 1 END) AS 'Juni'
,SUM(CASE WHEN MONTH(date) = 7 THEN 1 END) AS 'Juli'
,SUM(CASE WHEN MONTH(date) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(date) = 9 THEN 1 END) AS 'Sept'
,SUM(CASE WHEN MONTH(date) = 10 THEN 1 END) AS 'Okt'
,SUM(CASE WHEN MONTH(date) = 11 THEN 1 END) AS 'Nove'
,SUM(CASE WHEN MONTH(date) = 12 THEN 1 END) AS 'Desem'
,SUM(CASE WHEN YEAR(date) = 2006 THEN 1 END) AS 'TOTAL'
FROM table
WHERE YEAR(date) = 2006
GROUP BY type

This in combination with union the other 2 types results in what I'd like to do but then again, phuu this is some crappy approach :)

Any suggustions in how to solve this?
I.E I'd like to list each type count for each month like this

Jan Feb Mars ...
type1 23 43 45
type2 12 11 15
type3 54 55 65

Any hints?

/J

Maybe something like?

set nocount on

-- --
-- I am confused by the way that the 'type' column is laid
-- out. The table design seems to indicate that there are
-- separate columns for types 1, 2 and 3; however, the report
-- layout indicates that types 1, 2 and 3 are rather specific
-- instances of a single type column. Since the latter is
-- more commonly practiced, that is the way that I shall
-- approach the problem.
--
-- --
declare @.table table
( rid integer not null
primary key,
type varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number,
'Type' + convert (char(1), number%3 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from ( select type,
month (date) as [month],
count(*) as typeCount
from @.table
group by type,
month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 11 9 10 10 11 10 10 10 5 0 0 0
-- Type2 10 10 10 10 10 10 11 10 4 0 0 0
-- Type3 10 9 11 10 10 10 10 11 4 0 0 0

|||

Unfortunatly the types are 3 different columns (at design time this report was not considered).
To work around this I union these to one "alltypes" column. This is timeconsuming and I would prefere not to do so.

Part from that your approach works find (part from pivot, I'm on SQL server 2000, not a problem though).

Is there a workaround for the unions?

Thanks

|||Sure there is; please stand by|||

set nocount on

declare @.table table
( rid integer not null
primary key,
[type 1] varchar (10),
[type 2] varchar (10),
[type 3] varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number + 1,
'Type' + convert (char(1), number%7 + 1),
'Type' + convert (char(1), (number+1)%7 + 1),
'Type' + convert (char(1), (number+5)%7 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from( select value as Type,
month (date) as [Month],
count(*) as typeCount
from @.table
unpivot ( value for Type in ([Type 1],[Type 2],[Type 3])
) as unpiv
group by value, month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 14 12 12 14 13 12 14 13 6 0 0 0
-- Type2 14 12 13 13 14 12 14 13 6 0 0 0
-- Type3 14 12 13 12 14 13 13 14 5 0 0 0
-- Type4 13 12 14 12 14 13 12 14 6 0 0 0
-- Type5 12 12 14 13 13 13 13 14 5 0 0 0
-- Type6 13 12 14 13 12 14 13 13 5 0 0 0
-- Type7 13 12 13 13 13 13 14 12 6 0 0 0

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionSt ring"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
Thanks
Richard
It doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionSt ring"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionString"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
--
Thanks
RichardIt doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionString"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

Multiple columns

I'm search on multiple columns useing FREETEXTTABLE, is there a way to know
which column contained the search string? Be low is my query:
SELECT C_ID, ( C_LNAME + ', ' + C_FNAME) as C_NAME, C_SIT1, C_SIT2,
C_SIT3, C_SIT4, C_SIT5, C_SIT6, C_SIT7, C_SIT8, C_SIT9, C_SIT10,
c_city, c_zip, C_hphone, c_wphone, c_cphone, c_email, c_smkcomment,
c_rank_Comment, c_wage_comment, c_edname1, c_edname2, c_edname3,
c_wkemp1, c_wkemp2, c_wkemp3, c_wkcomment, c_refname1, c_refname2,
c_refname3, c_follow, c_comment, k.rank
FROM Candidates as c
inner join FREETEXTTABLE(Candidates, *, '"2 years experience"') as k
on C.C_ID = K.[KEY]
where k.rank > 2
order by k.rank desc
Well you can do something like this, but it does not perform well.
create table Candidates (
C_ID int identity not null constraint CandidatesPK primary key ,
C_LNAME varchar(50),
C_FNAME varchar(50))
insert into candidates (C_LNAME, C_FNAME) values('james', 'bond')
insert into candidates (C_LNAME, C_FNAME) values('james bond', 'test')
insert into candidates (C_LNAME, C_FNAME) values('test','test')
insert into candidates (C_LNAME, C_FNAME) values('test','james bond')
GO
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG candidates AS DEFAULT
GO
create fulltext index on candidates(C_LNAME, C_FNAME) key index CandidatesPK
GO
select * from candidates where contains(*,'"james bond"')
declare @.string varchar(20)
set @.string='"James Bond"'
select C_ID, case when a.[KEY] is not null then 'C_LNAME' when b.[KEY] is
not null then 'C_FNAME' end,C_LNAME, C_FNAME from candidates
left join (select * from containstable(candidates, C_LNAME,@.string)) as a on
a.[KEY]=Candidates.C_ID
left join (select * from containstable(candidates, C_FNAME,@.string)) as b on
b.[KEY]=Candidates.C_ID
where a.[KEY] is not null OR b.[KEY ]is not null
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rhogan" <rhogan@.discussions.microsoft.com> wrote in message
news:AC8BBE41-E95C-47B5-BF6A-B5213C830157@.microsoft.com...
> I'm search on multiple columns useing FREETEXTTABLE, is there a way to
> know
> which column contained the search string? Be low is my query:
> SELECT C_ID, ( C_LNAME + ', ' + C_FNAME) as C_NAME, C_SIT1, C_SIT2,
> C_SIT3, C_SIT4, C_SIT5, C_SIT6, C_SIT7, C_SIT8, C_SIT9, C_SIT10,
> c_city, c_zip, C_hphone, c_wphone, c_cphone, c_email, c_smkcomment,
> c_rank_Comment, c_wage_comment, c_edname1, c_edname2, c_edname3,
> c_wkemp1, c_wkemp2, c_wkemp3, c_wkcomment, c_refname1, c_refname2,
> c_refname3, c_follow, c_comment, k.rank
> FROM Candidates as c
> inner join FREETEXTTABLE(Candidates, *, '"2 years experience"') as k
> on C.C_ID = K.[KEY]
> where k.rank > 2
> order by k.rank desc