Please forgive me....

I know this has been talked about again, and again.. but
This is what i got..
1. 2x Windows 2003 Ent servers.. attached to a dell powervault 220s
storage array that has
a. 3x arrays, 1 for the the quorom, 1 for each clusternode's
data disk
2. SQL 2005 Installed with a default instance named lets say sql1 as
the virtual servername, and it's IP address
What the developers said they need / want is another "default" instance,
or something that from there programs just call a single name instead of
having to use the virtualservername\instance name so something more like
sql2, is this possible... i have been reading that it isn't.. that you will
end up with virtualservername\instance for anything other then the default
instance. Also i have been reading that you can alias the
virtualservername\instance in DNS with a SRV record, but i dont seem to have
a type of _sql or something listed in the srv records available to me on my
2000 AD,,, what am i missing to make this work the way the developers needs?
Please help me, thanks!
So what they're saying is they need sql2 instead of sql1\B? Have them go to
the CFO and cost justify why buying another cluster is worth them not having
to type those two extra characters. Let us know what he says.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Jeff Pancrazio" <jpancrazioNO@.SPAMgmail.com> wrote in message
news:uRT%23JEmjHHA.568@.TK2MSFTNGP02.phx.gbl...
>I know this has been talked about again, and again.. but
> This is what i got..
> 1. 2x Windows 2003 Ent servers.. attached to a dell powervault 220s
> storage array that has
> a. 3x arrays, 1 for the the quorom, 1 for each clusternode's
> data disk
> 2. SQL 2005 Installed with a default instance named lets say sql1
> as the virtual servername, and it's IP address
> What the developers said they need / want is another "default" instance,
> or something that from there programs just call a single name instead of
> having to use the virtualservername\instance name so something more like
> sql2, is this possible... i have been reading that it isn't.. that you
> will end up with virtualservername\instance for anything other then the
> default instance. Also i have been reading that you can alias the
> virtualservername\instance in DNS with a SRV record, but i dont seem to
> have a type of _sql or something listed in the srv records available to me
> on my 2000 AD,,, what am i missing to make this work the way the
> developers needs?
> Please help me, thanks!
>
|||Roger Wolter[MSFT] wrote:
> So what they're saying is they need sql2 instead of sql1\B? Have them
> go to the CFO and cost justify why buying another cluster is worth them
> not having to type those two extra characters. Let us know what he says.
>
Additionally: unless the developers are hard-coding the name of the
server into their apps (which would be the height of stupidity, IMO),
the 'virtualserver\instance' name should be transparently
interchangeable with the default instance's 'server' name.
My organization has several apps that were originally implemented on
individual hosts using the default instance on each host. We ponied up
for some big hardware to create a cluster, and now we run several named
instances on an active/active cluster without any special
support/assistance from the developers: we simply substituted the
'servername' with 'server\instance' in all the configuration locations
(whether it was an ODBC DSN, registry string or INI file) to get it to
'just work.'
AFAIK, this is supported because of the SQL Server driver, not the
creative coding of the application developer.

Pivot Table / CUBE ?

Please see the attached fileThere are two options.
If you know the column name than use the first option (see sample) if you do not know it than use the second option (it is dynamic execution).

Eyal

--Second option
CREATE PROC sp_CrossTab
@.table AS sysname, -- Table to crosstab
@.onrows AS nvarchar(128), -- Grouping key values (on rows)
@.onrowsalias AS sysname = NULL, -- Alias for grouping column
@.oncols AS nvarchar(128), -- Destination columns (on columns)
@.sumcol AS sysname = NULL -- Data cells
AS

DECLARE
@.sql AS varchar(8000),
@.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @.sql =
'SELECT' + @.NEWLINE +
' ' + @.onrows +
CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)
SET @.keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +
'FROM ' + @.table

EXEC (@.keyssql)

-- 6
DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
SET @.sql = @.sql + ',' + @.NEWLINE +
' SUM(CASE CAST(' + @.oncols +
' AS nvarchar(100))' + @.NEWLINE +
' WHEN N''' + @.key +
''' THEN ' + CASE
WHEN @.sumcol IS NULL THEN '1'
ELSE @.sumcol
END + @.NEWLINE +
' ELSE 0' + @.NEWLINE +
' END) AS c' + @.key

SELECT @.key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @.key
END

--7
SET @.sql = @.sql + @.NEWLINE +
'FROM ' + @.table + @.NEWLINE +
'GROUP BY ' + @.onrows + @.NEWLINE +
'ORDER BY ' + @.onrows

-- PRINT @.sql + @.NEWLINE -- For debug
EXEC (@.sql)

GO

--First option
Use pubs
GO

create table strings
(
groupcol char(1) not null,
keycol int not null,
string varchar(10) not null
)
GO

insert into strings values('a', 11, 'strA1')
insert into strings values('a', 152, 'strA2')
insert into strings values('b', 101, 'strB1')
insert into strings values('b', 201, 'strB2')
insert into strings values('b', 307, 'strB3')
insert into strings values('b', 499, 'strB4')
GO

select groupcol,
max(case when rownum = 1 then string end) as str1,
max(case when rownum = 2 then string end) as str2,
max(case when rownum = 3 then string end) as str3,
max(case when rownum = 4 then string end) as str4,
max(case when rownum = 5 then string end) as str5
from (select *, (select count(*)
from strings as s2
where s2.groupcol = s1.groupcol
and s2.keycol <= s1.keycol) rownum
from strings as s1) as s
group by groupcol
GO

DROP Table strings

GO|||Thanks a lot|||I use the second option (dynamic execution)|||I use stored procedure "sp_CrossTab", is OK (testing with "SQL ExecMS")

If I want to use this stored procedure in VB , did not returns a recordset.

This is VB Code: (Where have I did it wrong ????)

Private cn As New ADODB.Connection
Private cmd As New ADODB.Command
Private rs As New ADODB.Recordset

'--------------------
Private Sub Form_Load()

Dim SirConectare_SQL As String

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Integrated Security=SSPI" & _
";Persist Security Info=False" & _
";Initial Catalog='" & "Test" & "'" & _
";Data Source='" & "Acasa" & "'"

With cn
.ConnectionString = SirConectare_SQL
.Open
.CursorLocation = adUseClient

End With

End Sub
'-----------------------
Private Sub Command1_Click()

cmd.ActiveConnection = cn
cmd.CommandText = "sp_CrossTab"
cmd.CommandType = adCmdStoredProc

cmd.Parameters(1).Value = "Table1"
cmd.Parameters(2).Value = "Day"
cmd.Parameters(3).Value = "XXXXX"
cmd.Parameters(4).Value = "Grup"
cmd.Parameters(5).Value = "Value_1"

Set rs = cmd.Execute

MsgBox rs.RecordCount

End Sub|||I forgot this ...

set nocount on

.....................

It is OK, thanks