Cluster services overhead

Consider the scenario ...
Instance 1 of Sql Server running on Compaq with MSA1000
disks
Instance 2 of Sql Server running on Compaq with MSA1000
disks BUT in a clustered environment
We are finding that Instance 1 offers better performance
than 2 for the same configuration (memory, sql version,
windows etc). Could the use of cluster services for
clustering be causing the overhead?
TIA,
JackIt is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx
.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>|||I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
.clustering newsgroup. Hopefully the experts that hang out there will hav
e
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx
.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>|||I am unfamiliar with the Compaq hardware mentioned, but I do know that
hardware that uses SCSI technology or host controller based caching can
suffer greatly in a clustered environment. All host caching has to be
turned off in a clustered environment to avoid data integrity problems.
This can severely impact disk performacne, especially with disk writes in a
RAID-5 configuration. While this may not apply to your circumstance, it is
the primary reason I do not recommend clustering with SCSI hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx
.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster services overhead

Consider the scenario ...
Instance 1 of Sql Server running on Compaq with MSA1000
disks
Instance 2 of Sql Server running on Compaq with MSA1000
disks BUT in a clustered environment
We are finding that Instance 1 offers better performance
than 2 for the same configuration (memory, sql version,
windows etc). Could the use of cluster services for
clustering be causing the overhead?
TIA,
Jack
It is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>
|||I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
..clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>
|||I am unfamiliar with the Compaq hardware mentioned, but I do know that
hardware that uses SCSI technology or host controller based caching can
suffer greatly in a clustered environment. All host caching has to be
turned off in a clustered environment to avoid data integrity problems.
This can severely impact disk performacne, especially with disk writes in a
RAID-5 configuration. While this may not apply to your circumstance, it is
the primary reason I do not recommend clustering with SCSI hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster services overhead

It is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>
I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
..clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster services overhead

Consider the scenario ...
Instance 1 of Sql Server running on Compaq with MSA1000
disks
Instance 2 of Sql Server running on Compaq with MSA1000
disks BUT in a clustered environment
We are finding that Instance 1 offers better performance
than 2 for the same configuration (memory, sql version,
windows etc). Could the use of cluster services for
clustering be causing the overhead?
TIA,
JackIt is possible, although I would not expect the clustering technology to =have a noticable impact on performance. You talk about instances. Are =you running multiple instances of SQL Server on the same hardware, or by ="instance" do you mean Server A (standalone box) and Server B (clustered =box)? If you are talking about multiple instances on one server are the =instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to =the .clustering newsgroup. Hopefully the experts that hang out there =will have additional comments orideas.
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> > Consider the scenario ...
> > Instance 1 of Sql Server running on Compaq with MSA1000 > disks > > Instance 2 of Sql Server running on Compaq with MSA1000 > disks BUT in a clustered environment
> > We are finding that Instance 1 offers better performance > than 2 for the same configuration (memory, sql version, > windows etc). Could the use of cluster services for > clustering be causing the overhead?
> > TIA,
> Jack
>|||I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
.clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
--
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>|||I am unfamiliar with the Compaq hardware mentioned, but I do know that
hardware that uses SCSI technology or host controller based caching can
suffer greatly in a clustered environment. All host caching has to be
turned off in a clustered environment to avoid data integrity problems.
This can severely impact disk performacne, especially with disk writes in a
RAID-5 configuration. While this may not apply to your circumstance, it is
the primary reason I do not recommend clustering with SCSI hardware.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster recovery from node failure

Cluster services gives the high availability needed - that is great.
But I have never seen any discussion about what happens when a node
fails - what do you do to get everything back to the active-passive
tandem.

I imagine there is not much difference in terms of recovery procedure
for either active or passive node. So I'm just going to make up a
scenario that we have encountered. The system hard drive (not the
shared disk) on primary node fails. Cluster fails over to the passive
node. Following are the problems I have at hand:
-After installing windows, I need to install driver and configure the
permission to access the SAN. There is no way I could do it since the
secondary node has exclusive access to the disks.
-Imagine I got that working, is there anyway to install SQL so SQL
would know this server used to be the primary node and attach the DB
and translog automatically
-Finally, there is no proper way to apply SQL 2000 service pack 3a.
Originally when the cluster was fully functional, the service pack was
applied to active node and that automatically upgrades passive node.
Now we have a machine without 3a and a machine with 3a already
installed. See any problem?

Consider all of the above as this one big question: What is a proper
procedure to restore a cluster when one of the node goes down? Whether
it's the active or passive node."gotdough" <praemonitus@.hotmail.com> wrote in message
news:1ad01306.0409120058.3df26726@.posting.google.c om...
> Cluster services gives the high availability needed - that is great.
> But I have never seen any discussion about what happens when a node
> fails - what do you do to get everything back to the active-passive
> tandem.
> I imagine there is not much difference in terms of recovery procedure
> for either active or passive node. So I'm just going to make up a
> scenario that we have encountered. The system hard drive (not the
> shared disk) on primary node fails. Cluster fails over to the passive
> node. Following are the problems I have at hand:
> -After installing windows, I need to install driver and configure the
> permission to access the SAN. There is no way I could do it since the
> secondary node has exclusive access to the disks.
> -Imagine I got that working, is there anyway to install SQL so SQL
> would know this server used to be the primary node and attach the DB
> and translog automatically
> -Finally, there is no proper way to apply SQL 2000 service pack 3a.
> Originally when the cluster was fully functional, the service pack was
> applied to active node and that automatically upgrades passive node.
> Now we have a machine without 3a and a machine with 3a already
> installed. See any problem?
> Consider all of the above as this one big question: What is a proper
> procedure to restore a cluster when one of the node goes down? Whether
> it's the active or passive node.

This KB article might help:

http://support.microsoft.com/defaul...0&Product=sql2k

You should probably also post this in microsoft.public.sqlserver.clustering
to see if you get a better response.

Simon

Cluster MAS 2000

We are planning to cluster SQL Server 2005 (Active-Active). Can we also
cluster Microsoft Analysis Services 2000 on the same nodes ? If we can, what
are the issues we need to be aware ?
We are not ready to go to Analysis Services 2005 yet.
Thanks.Take a look at the following MS KB:
http://support.microsoft.com/kb/308023
--
Ekrem Ã?nsoy
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:1D7EABFE-1FCC-4C26-A211-3156A3E9E4DC@.microsoft.com...
> We are planning to cluster SQL Server 2005 (Active-Active). Can we also
> cluster Microsoft Analysis Services 2000 on the same nodes ? If we can,
> what
> are the issues we need to be aware ?
> We are not ready to go to Analysis Services 2005 yet.
>
> Thanks.

Cluster Installation Failure

Thanks in advance for any help.
A SQL 2005 Failover Clustering installation (from the RTM disks) failed on
Database Services. Please see messages below. Windows cluster server name
is COMDEVDB01 (Windows 2003), primary node = COMDEVCLU01, secondary node =
COMDEVCLU02. SQL cluster virtual name = COMDEVSQLCLU, named instance =
COMDEVPROD. I installed from COMDEVCLU01. Should I install from COMDEVDB01
while COMDEVCLU01 is the active node (i.e., I will be on COMDEVCLU01)?
Earlier install failed on SQLBrowser errors and rolled back. Before the
cluster install two non-clustered SQL 2005 named instances had been installed
on COMDEVCLU01 and SP2 had been applied (both current versions = 3152).
After the SQLBrowser failure I installed a non-clustered SQL 2005 named
instance on COMDEVCLU02 with Database Services and Integration Services (also
version 3152). After that, the cluster install on COMDEVCLU01 failed with
these errors but did not rollback.
Should I try to install SP2 for the SQL cluster from COMDEVCLU01?
Can I "complete" this installation (save it) or will I have to uninstall and
re-install?
Should I uninstall all SQL Server instances on COMDEVCLU01 and install the
clustered instance first?
summary.txt message:
Machine : COMDEVCLU01
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0006_COMDEVCLU01_SQL.l og
Last Action : ComponentUpgrade
Error String : The setup has encountered an error while Upgrading
Components. The error: -2147019873
Error message: The group or resource is not in the correct state to perform
the requested operation.
Error Number : 29550
SQLSetup0006_COMDEVCLU01_SQL.log message:
MSI (s) (AC:14) [15:52:23:016]: Product: Microsoft SQL Server 2005 - Update
'Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000)' could not
be installed. Error code 1603.
MichaelA_CCGA
Michael:
I will be posting a similar question to yours entitled "Failed SQL Cluster
Advice." That hopefully will get answered as well.
In the meantime you mentioned RTM Disks. Are these CDs and did your install
fail when you put in Disk 2 "Tools" This is my issue and I have googled to
find that this is a known issue and the resolution is copy both disks to a
common path.
Was this your issue as well?
Thanks, emagidson
"MichaelA_CCGA" wrote:

> Thanks in advance for any help.
> A SQL 2005 Failover Clustering installation (from the RTM disks) failed on
> Database Services. Please see messages below. Windows cluster server name
> is COMDEVDB01 (Windows 2003), primary node = COMDEVCLU01, secondary node =
> COMDEVCLU02. SQL cluster virtual name = COMDEVSQLCLU, named instance =
> COMDEVPROD. I installed from COMDEVCLU01. Should I install from COMDEVDB01
> while COMDEVCLU01 is the active node (i.e., I will be on COMDEVCLU01)?
> Earlier install failed on SQLBrowser errors and rolled back. Before the
> cluster install two non-clustered SQL 2005 named instances had been installed
> on COMDEVCLU01 and SP2 had been applied (both current versions = 3152).
> After the SQLBrowser failure I installed a non-clustered SQL 2005 named
> instance on COMDEVCLU02 with Database Services and Integration Services (also
> version 3152). After that, the cluster install on COMDEVCLU01 failed with
> these errors but did not rollback.
> Should I try to install SP2 for the SQL cluster from COMDEVCLU01?
> Can I "complete" this installation (save it) or will I have to uninstall and
> re-install?
> Should I uninstall all SQL Server instances on COMDEVCLU01 and install the
> clustered instance first?
> summary.txt message:
> ----
> Machine : COMDEVCLU01
> Product : Microsoft SQL Server 2005
> Product Version : 9.00.1399.06
> Install : Failed
> Log File : C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0006_COMDEVCLU01_SQL.l og
> Last Action : ComponentUpgrade
> Error String : The setup has encountered an error while Upgrading
> Components. The error: -2147019873
> Error message: The group or resource is not in the correct state to perform
> the requested operation.
> Error Number : 29550
> ----
> SQLSetup0006_COMDEVCLU01_SQL.log message:
> ----
> MSI (s) (AC:14) [15:52:23:016]: Product: Microsoft SQL Server 2005 - Update
> 'Hotfix 3024 for SQL Server Database Services 2005 ENU (KB000000)' could not
> be installed. Error code 1603.
> --
> MichaelA_CCGA
|||Sorry I dropped out of the discussion but I wanted to post the question
before I took a few days off and I'm just now catching up. I have printed
your exchange with Geoff and I will review that in more detail.
I did not have the "2nd CD" problem but the information about uninstalling
may prove very useful.
Thanks again, and I'll check back in tomorrow or Thursday.
MichaelA_CCGA
"EMagidson" wrote:
[vbcol=seagreen]
> Michael:
> I will be posting a similar question to yours entitled "Failed SQL Cluster
> Advice." That hopefully will get answered as well.
> In the meantime you mentioned RTM Disks. Are these CDs and did your install
> fail when you put in Disk 2 "Tools" This is my issue and I have googled to
> find that this is a known issue and the resolution is copy both disks to a
> common path.
> Was this your issue as well?
> Thanks, emagidson
> "MichaelA_CCGA" wrote:

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Procedure not beeing seen by Reporting Services

I have written a CLR stored procedure and loaded it up into SQL... I
can run from there but when I reference it from Reporting Services, I
get an error saying that the stored procedure does not exist. IT
DOES!!!
There was a post in this group where the answer was to have the Command
Type set to Text and use EXEC... The challenge with that solution is
passing parameters doesn't seem to work this way.
I have seen where people have written a T-SQL "wrapper" for the
procedure but that yeilds the same results for me.
Any help would be appreciated.Make sure the login username and password as defined in your reporting
services datasource does have rights on the stored procedure.
Simply right click the stored procedure in enterprise manager, click
permissions and grant 'exec' rights to the user being used in the
datasource.|||I am not sure why I would need to that since we are running under
integrated security. I did try to give myself explicit permissions on
the proc as you suggested and still no luck. Does the assembly need to
be strong named?
nickismyname wrote:
> Make sure the login username and password as defined in your reporting
> services datasource does have rights on the stored procedure.
> Simply right click the stored procedure in enterprise manager, click
> permissions and grant 'exec' rights to the user being used in the
> datasource.|||Before continuing, make sure that CLR Integration is enabled on the SQL
server. To do this, simply execute the system stored procedure
sp_configure on the SQL Server with advanced options enabled:
EXEC sp_configure @.configname = 'Show Advanced Options', @.configvalue =1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure
Look for a record where the name is clr enabled and check its run_value
- it should be 1. If it's not, you'll need to set it to 1 in order to
to enable CLR Integration in SQL Server 2005. To do this, execute the
following T-SQL code:
EXEC sp_configure @.configname = 'clr enabled', @.configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
There are three ways you can execute the above code against the SQL
Server:
SQL Server 2000 Query Analyzer
SQL Server 2005 SQLCMD Console Utility
SQL Server 2005 Management Studio|||It is... That was one of the first things that I did... The whole MS
"off-by-default" thing on their new baby servers can be frustrating.
Plus the proc wouldn't run from Sql Management Studio if it was off...
I did get a T-Sql "wrapper" proc to work (not sure why it wasn't
before) but I don't think this is the best solution. If you have any
other thoughts, I would love to hear them...
Thanks again
nickismyname wrote:
> Before continuing, make sure that CLR Integration is enabled on the SQL
> server. To do this, simply execute the system stored procedure
> sp_configure on the SQL Server with advanced options enabled:
> EXEC sp_configure @.configname = 'Show Advanced Options', @.configvalue => 1
> RECONFIGURE WITH OVERRIDE
> GO
> EXEC sp_configure
> Look for a record where the name is clr enabled and check its run_value
> - it should be 1. If it's not, you'll need to set it to 1 in order to
> to enable CLR Integration in SQL Server 2005. To do this, execute the
> following T-SQL code:
> EXEC sp_configure @.configname = 'clr enabled', @.configvalue = 1
> RECONFIGURE WITH OVERRIDE
> GO
> There are three ways you can execute the above code against the SQL
> Server:
> SQL Server 2000 Query Analyzer
> SQL Server 2005 SQLCMD Console Utility
> SQL Server 2005 Management Studio

CLR integration - Could not find Type '?' in assembly '?'.

Hi,

I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.

The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.

I have created the assembly in sqlserver thus:

create assembly PinCodeLoader from
'C:\PinCodeLoader\PinCodeLoader\PinCodeLoader\bin\Debug\PinCodeLoader.dll'
with permission_set = external_access

But when I try to reference the assembly from a stored proc

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main

I get the following error:

Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.

I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows

namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{

Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.

Has anybody come across this or can they offer any assistance?

Many thanks,

Paul

Hi, try to compile the .vb file with this command line:

csc.exe /t:library PinCodeLoader.bv (Put the correct name of the .vb file)

If this work look at the properties of the project and search for 'root namespace' (or something similar), and delete it, then try again compiling the entire solution from de IDE...

Good luck

Alejandro F.

|||

You need to include the namespace as well:

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.[PinCodeLoader.PinCodeloader].Main

However, you still won't be able to create your proc because SQL can't map nvarchar(1024) to the string array args.

|||

Thanks for your replies guys. I didn't actually try that method but found a way around it.

In my c# program I removed the reference to a namespace, as I was told it's not really necessary and changed the code as follows.

public class PinCodeLoad

{

public static void Main(string filename,string datasource,Int32 timeout, string companyname)

{

So instead of using the args parameter I'm implictly defining them. Plus I preceded my class and method with public

I kept the stored proc as before.

I hope that helps anybody else.

Paul

Closing IE Window used for exporting...

Taken from Reporting Services 2000 Help - 'When you click Export, the report
opens in a new browser window, in a viewer that is associated with the export
format. If in Step 2 you clicked TIFF, the report is displayed in a viewer
that supports TIFF file types.'
Is there any way to close this new browser window so it does not stay behind
and make the user close it?
Thanks for any input!Hi rheath,
Welcome to use MSDN Managed Newsgroup Support.
It is a by design issue in Reporting Service 2000 and was resolved in
Reporting Service 2005. If you try to export a report in Reporting Services
2005, the browser window will close and do not need to manually close it.
Thanks for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
Do the reports have to be designed using Visual Studio 2005. I ask because
we are developing the reports using Visual Studio 2003 and depolying to a
Reporting Services 2005 server and I still get the browser window that does
not close when exporting.
Thanks,
Russ
"Wei Lu" wrote:
> Hi rheath,
> Welcome to use MSDN Managed Newsgroup Support.
> It is a by design issue in Reporting Service 2000 and was resolved in
> Reporting Service 2005. If you try to export a report in Reporting Services
> 2005, the browser window will close and do not need to manually close it.
> Thanks for your understanding.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Russ,
Thanks for the update.
I have tested on my side. I deploy a report to Report Services 2005 using
VS 2003. I get the browser closed when exporting.
Would you please test on another machine to access the report server and
try to export the report?
Does this issue occur?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

client side print button not visible even in SQL Server 2005 Express SP 2.0

Hi All!

i installed SQL Server 2005 express edition Service Pack 2.0, SQL server Advance Services, and Toolkit . Still Print button in client side report is not visible in web form.

please help me.

thanks in advance.

rkraju:

Hi All!

i installed SQL Server 2005 express edition Service Pack 2.0, SQL server Advance Services, and Toolkit . Still Print button in client side report is not visible in web form.

please help me.

thanks in advance.

Wel I guess we cannot do that...printing is not available on client SSRS...Too bad...And just to let you know, if you need to send query parameters to the report forget the client SSRS again...it sucks...you have to install the report on the Web server and that way yes you can print and send query parameters and so long...other way no solution...

Read here...I'm feeling frustrated on this...

http://blogs.msdn.com/mohamed_sharafs_blog/archive/2005/12/20/LocalReportCustomCode.aspx

However if you've found another solution please let me know!!!Thanks a lot!

Client Installation Question

Hi, all,
I'm having one of those "dense" moments. We've installed Reporting Services
on our SQL Cluster. Have a workstation running .NET Framework and Visual
Studio.NET. I can't figure out how to install the client portion of
Reporting Services onto the workstation. Can anyone point me in the right
direction? Thanks.
MikeRun the same setup as you did for installing Reporting Services on your
server and just install the client tools portion of it. As a matter of fact
if IIS is not running on the client then the client tools should be the only
option you have to install.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Merker" <zerenia@.operamail.com> wrote in message
news:C98019A6-2032-4293-BFC7-CF92FC25BD11@.microsoft.com...
> Hi, all,
> I'm having one of those "dense" moments. We've installed Reporting
Services
> on our SQL Cluster. Have a workstation running .NET Framework and Visual
> Studio.NET. I can't figure out how to install the client portion of
> Reporting Services onto the workstation. Can anyone point me in the right
> direction? Thanks.
> Mike|||Bruce,
You were right on. We kept stopping at the requirements screen as we
thought we were being told that the product would not install.
Mike
"Bruce L-C [MVP]" wrote:
> Run the same setup as you did for installing Reporting Services on your
> server and just install the client tools portion of it. As a matter of fact
> if IIS is not running on the client then the client tools should be the only
> option you have to install.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Mike Merker" <zerenia@.operamail.com> wrote in message
> news:C98019A6-2032-4293-BFC7-CF92FC25BD11@.microsoft.com...
> > Hi, all,
> >
> > I'm having one of those "dense" moments. We've installed Reporting
> Services
> > on our SQL Cluster. Have a workstation running .NET Framework and Visual
> > Studio.NET. I can't figure out how to install the client portion of
> > Reporting Services onto the workstation. Can anyone point me in the right
> > direction? Thanks.
> >
> > Mike
>
>

Client connectivity to SQL Server 2005

Hello!
I am developing an application that will connect to a SQL Server 2005
(including Analysis Services) database. The SQL database will reside on
a server, not on the client my app will be installed on.
I am creating a setup program and I guess that I need to redistribute
the client connectivity components in mysetup program. The problem is
that I don't know which files contains the client connectivity. As my
software might be downloaded from the internet, I need a fool-prof
solution.
I've searched msdn, but not found any useful info regarding this
delicate matter.
TIA
Clarkieclarkbones@.rocketmail.com schrieb:
> Hello!
> I am developing an application that will connect to a SQL Server 2005
> (including Analysis Services) database. The SQL database will reside on
> a server, not on the client my app will be installed on.
> I am creating a setup program and I guess that I need to redistribute
> the client connectivity components in mysetup program. The problem is
> that I don't know which files contains the client connectivity. As my
> software might be downloaded from the internet, I need a fool-prof
> solution.
> I've searched msdn, but not found any useful info regarding this
> delicate matter.
>
> TIA
> Clarkie
For general SQL Server connectivity (pre Sql Server 2005) you need to
link them to the MDAC Download Site available to the public:
http://www.msdn.microsoft.com/data/mdac/
For SQL Server 2005 Client connectivity you need to link them to the
SQL Native Client
http://www.msdn.microsoft.com/data/...ve/default.aspx
btw: On these sites you may also find information on howto include these
packages direcly into your own software and if its allowed to do so or not.
hth
Stefka

Click-through on graph bars and objects?

Can you do click-throughs on Reporting Services 2005 Graphs whereas if I have created a dashboard somehow with 4 reporting services charts on our Intranet, that a user can click on a bar in the graph and it would drill down into an automated click-through report for that user?it is possible|||Does the earlier version of Report Services (2000, not 2005) support click-through on graph bars as well?|||Peter, I don't think so...|||By the way, I'm loving SSRS 2005, it rocks compared to Crystal! much easier and no more Crystal syntax! only VB.NET! can't get much better than that.|||

Yes, RS 2000 has the same drill through capabilities in charts.

-- Robert

|||

Can you go over how this is accomplished?
Thanks!!

Click-through on graph bars and objects?

Can you do click-throughs on Reporting Services 2005 Graphs whereas if I have created a dashboard somehow with 4 reporting services charts on our Intranet, that a user can click on a bar in the graph and it would drill down into an automated click-through report for that user?it is possible|||Does the earlier version of Report Services (2000, not 2005) support click-through on graph bars as well?|||Peter, I don't think so...|||By the way, I'm loving SSRS 2005, it rocks compared to Crystal! much easier and no more Crystal syntax! only VB.NET! can't get much better than that.|||

Yes, RS 2000 has the same drill through capabilities in charts.

-- Robert

|||

Can you go over how this is accomplished?
Thanks!!

Click-through on graph bars and objects?

Can you do click-throughs on Reporting Services 2005 Graphs whereas if I have created a dashboard somehow with 4 reporting services charts on our Intranet, that a user can click on a bar in the graph and it would drill down into an automated click-through report for that user?it is possible|||Does the earlier version of Report Services (2000, not 2005) support click-through on graph bars as well?|||Peter, I don't think so...|||By the way, I'm loving SSRS 2005, it rocks compared to Crystal! much easier and no more Crystal syntax! only VB.NET! can't get much better than that.|||

Yes, RS 2000 has the same drill through capabilities in charts.

-- Robert

|||

Can you go over how this is accomplished?
Thanks!!

Clicking print from Reporting Services report cause computer to re

I have a report developed using Reporting Services 2005 with SQL Server 2005.
It runs just fine, and printed fine up until a few days ago. Now, after I
run the report and it displays on the screen, clicking print causes the
computer to reboot. Not all the reports do this, just this one. I don't
know of anything that has changed that could cause this problem. Anyone have
a similar experience? The report uses a stored procedure to build a table.
But, again it did work o.k. until a few days ago.
ThanksSAME PROBLEM HERE !!
Our client called us yesterday, everything worked fine and now clicking the
print button on 1 or 2 reports just REBOOT the computer !
We are using Reporting Services 2000 with SQL Server 2000.
Please help!
"Glenna" wrote:
> I have a report developed using Reporting Services 2005 with SQL Server 2005.
> It runs just fine, and printed fine up until a few days ago. Now, after I
> run the report and it displays on the screen, clicking print causes the
> computer to reboot. Not all the reports do this, just this one. I don't
> know of anything that has changed that could cause this problem. Anyone have
> a similar experience? The report uses a stored procedure to build a table.
> But, again it did work o.k. until a few days ago.
> Thanks|||We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
patches?
"KhAoS" wrote:
> SAME PROBLEM HERE !!
> Our client called us yesterday, everything worked fine and now clicking the
> print button on 1 or 2 reports just REBOOT the computer !
> We are using Reporting Services 2000 with SQL Server 2000.
> Please help!
> "Glenna" wrote:
> > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > It runs just fine, and printed fine up until a few days ago. Now, after I
> > run the report and it displays on the screen, clicking print causes the
> > computer to reboot. Not all the reports do this, just this one. I don't
> > know of anything that has changed that could cause this problem. Anyone have
> > a similar experience? The report uses a stored procedure to build a table.
> > But, again it did work o.k. until a few days ago.
> >
> > Thanks|||On Apr 18, 10:36 am, pbriggsiberia
<pbriggsibe...@.discussions.microsoft.com> wrote:
> We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> patches?
>
> "KhAoS" wrote:
> > SAME PROBLEM HERE !!
> > Our client called us yesterday, everything worked fine and now clicking the
> >printbutton on 1 or 2 reports justREBOOTthe computer !
> > We are using Reporting Services 2000 with SQL Server 2000.
> > Please help!
> > "Glenna" wrote:
> > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > run the report and it displays on the screen, clickingprintcausesthe
> > > computer toreboot. Not all the reports do this, just this one. I don't
> > > know of anything that has changed that could cause this problem. Anyone have
> > > a similar experience? The report uses a stored procedure to build a table.
> > > But, again it did work o.k. until a few days ago.
> > > Thanks- Hide quoted text -
> - Show quoted text -
I am having the same problem. Has anyone found the solution to this?|||Found the solution on another forum!!
The problem come from a recent update patch from microsoft that affects some
printers drivers.
To get rid of the problem, install this hotfix (KB935843) :
http://www.microsoft.com/downloads/details.aspx?familyid=69443B00-F831-4785-B292-DBF8CF002C6A&displaylang=en
More infos : http://support.microsoft.com/?kbid=935843
"jrichardson2@.chkenergy.com" wrote:
> On Apr 18, 10:36 am, pbriggsiberia
> <pbriggsibe...@.discussions.microsoft.com> wrote:
> > We rae now having the same issues...SQL RS 2005, SQL 2000...any hotfixes or
> > patches?
> >
> >
> >
> > "KhAoS" wrote:
> > > SAME PROBLEM HERE !!
> > > Our client called us yesterday, everything worked fine and now clicking the
> > >printbutton on 1 or 2 reports justREBOOTthe computer !
> > > We are using Reporting Services 2000 with SQL Server 2000.
> >
> > > Please help!
> >
> > > "Glenna" wrote:
> >
> > > > I have a report developed using Reporting Services 2005 with SQL Server 2005.
> > > > It runs just fine, and printed fine up until a few days ago. Now, after I
> > > > run the report and it displays on the screen, clickingprintcausesthe
> > > > computer toreboot. Not all the reports do this, just this one. I don't
> > > > know of anything that has changed that could cause this problem. Anyone have
> > > > a similar experience? The report uses a stored procedure to build a table.
> > > > But, again it did work o.k. until a few days ago.
> >
> > > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> I am having the same problem. Has anyone found the solution to this?
>