Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

Additional Problems with Reporting Services

I am trying to use Reporting Services but is proving more difficult than I envisaged. Here's the problem, when I try to use management studio to change the Server type to a Report server, it is disabled. Hence I cannot change it. When I read msdn, it stated that this was caused by the fact that report services was not installed. However, when I look at SQL Configuration Manager, it shows that Report Services is running. Am I not understanding something? What am I doing wrong?I know that in computing, that you should not do the same thing over and expect to get different results! I may have now have to disagree with that tenet. Somehow, magically, I am now able to set up reports in Business Intelligence, but I am still unable to change my Server type when Management Studio is started. May I get a response from one of these czars or do I have to put my fate/faith in magic?|||

Don't know if I'm a czar, but I have an answer. :)

This is by design, Management Studio Express does not support connections to Reporting Service. In fact, it only supports connections to Relational Databases. You would need to have the full version of Management Studio from one of the paid Editions of SQL Server to support connections to other services.

Mike

additional match information from CONTAINSTABLE

Is there a way to get additional match information from SQL Server
2000 CONTAINSTABLE? For example:
If my text data is:
The black bird and the blue bird...
01234567890123456789012678901234
And I search for bird, Can I get results that tell me that 2
occurances were found inside the data at locations 10 and 21?
no, there is no way to do this. You can get some of this functionality if
you dump your database content into the file system and then use Indexing
Services hit highlighting features to mark up your content in a web page.
"madmike" <mikek@.cs.cmu.edu> wrote in message
news:cb16b300.0407061229.c1f707d@.posting.google.co m...
> Is there a way to get additional match information from SQL Server
> 2000 CONTAINSTABLE? For example:
> If my text data is:
> The black bird and the blue bird...
> 01234567890123456789012678901234
> And I search for bird, Can I get results that tell me that 2
> occurances were found inside the data at locations 10 and 21?
|||MadMike,
While Hilary is correct in that you cannot get the specific info you're
requesting from CONTAINSTABLE or even CONTAINS, you *might* get somewhat
close to your requirements via using PatIndex. For example, if you could
work with getting a range of words, plus and minus distance from the search
word, you could do something like the below query using a table (pub_info)
in the Pubs database that is already FT-enabled on the TEXT column
(pr_info), you could use the following SQL code to get the results you want:
-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books)
-- and the near by words from 20 characters before to 100 characters after
the searched keyword(books).
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
/* returns the following results:
pub_id
-- ---
9952 t data for Scootney Books, publisher 9952 in the pubs database.
Scootney Books is located in New Yor
0736 t data for New Moon Books, publisher 0736 in the pubs database. New
Moon Books is located in Boston,
(2 row(s) affected)
*/
This might start you thinking in other terms on how to get your
requirements, as they say there are many ways to skin a cat!
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uPD$qt6YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> no, there is no way to do this. You can get some of this functionality if
> you dump your database content into the file system and then use Indexing
> Services hit highlighting features to mark up your content in a web page.

> "madmike" <mikek@.cs.cmu.edu> wrote in message
> news:cb16b300.0407061229.c1f707d@.posting.google.co m...
>
sql

additional logg for DTS_DataPump Task inDTS Package

hi
any one knows how can log in a dts package othere than the defalt log .

i can work it out using DTSPackageLog.WriteStringToLog using a seperate activex task in my package .

the problem is that in my package there are more than 15 dts datapump tasks ,so to make log for each task i have to use 15 such activex task.......

so any one know how we can use DTSPackageLog.WriteStringToLog
or any thing similar in dts datapump task so that we can know the beginning and ending of individual task

abhiCheck this link (http://vyaskn.tripod.com/sql_server_dts_error_file.htm) from Vyas.

Additional JOIN altering values

Can anyone see why I would get different GrossSales values by adding the
JOIN on LaborJobCosts?
****************************************
******
This gives the correct GrossSales values:
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales
FROM Sales S
RIGHT JOIN SaleCodes SC
ON SC.ItemSaleCode = S.ItemSaleCode
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (correct):
ItemSaleCode GrossSales
C 373807.08
D 39213.52
P 113303.00
R 204072.92
S 119939.00
W 506886.13
****************************************
******
Now if I add a JOIN for the LaborCosts table, I get incorrect values for my
GrossSales (but the LaborCosts are correct!):
DECLARE @.BeginSaleDate datetime
DECLARE @.EndSaleDate datetime
SET @.BeginSaleDate = '6/1/2004'
SET @.EndSaleDate = '6/1/2005'
SELECT
SC.ItemSaleCode,
ISNULL(SUM(ISNULL(S.TotalSaleAmount,0)),0) as GrossSales,
ISNULL(SUM(ISNULL(LC.LaborCost,0)), 0) AS LaborCosts
FROM Sales S
RIGHT JOIN SaleCodes SC ON SC.ItemSaleCode = S.ItemSaleCode
LEFT OUTER JOIN LaborJobCosts LC ON LC.SalesID = S.SalesID
WHERE S.CompletedDate BETWEEN @.BeginSaleDate AND @.EndSaleDate
GROUP BY SC.ItemSaleCode
Results (inflated and incorrect):
ItemSaleCode GrossSales
C 936678.78
D 29213.52
P 300171.00
R 264072.84
S 207079.00
W 529586.13
****************************************
******
CREATE TABLE [dbo].[Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
[CompletedDate] [smalldatetime] NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SaleCodes] (
[SaleCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemSaleCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LaborJobCosts] (
[LaborCostsID] [int] IDENTITY (1, 1) NOT NULL ,
[SalesID] [int] NULL ,
[LaborCost] [money] NULL
) ON [PRIMARY]
GOIt looks like SalesId isn't unique in the LaborJobCosts table. Try the
following query. Notice that RIGHT JOIN is redundant in your original - the
WHERE clause turns it into an INNER JOIN anyway. I've also taken out the
ISNULLs from inside the SUM functions - they don't achieve anything except
possibly slow things down.
It helps if you include keys and constraints with your DDL and also post
INSERT statements for some sample data. What you did post tells us that the
LaborJobCosts doesn't have any key other than the IDENTITY column. That's a
potentially serious design flaw.
SELECT SC.itemsalecode,
ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
ISNULL(SUM(LC.laborcost),0) AS laborcosts
FROM Sales S
JOIN SaleCodes SC
ON SC.itemsalecode = S.itemsalecode
LEFT JOIN
(SELECT salesid, SUM(laborcost) AS laborcost
FROM LaborJobCosts
GROUP BY salesid) AS LC
ON S.salesid = LC.salesid
WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
GROUP BY SC.itemsalecode
David Portas
SQL Server MVP
--|||hi
you might be getting a lot rows from the query and they are getting
hidden because of the SUM and GROUP BY,
please try to remove the SUM and GROUP BY and see the duplicate rows.
once the duplicate rows are eliminated then u can see the expected
results again
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||David Portas wrote:
> I've also taken out the ISNULLs from inside the SUM functions
> - they don't achieve anything except possibly slow things down.
They MAY achieve something: if there are NULL-s in that column, the
ISNULL from inside the SUM prevents the "Warning: Null value is
eliminated by an aggregate or other SET operation.". For example:
CREATE TABLE Test (
ID int primary key,
Value int NULL
)
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
SET NOCOUNT ON
INSERT INTO Test VALUES (1, 100)
INSERT INTO Test VALUES (2, 100)
INSERT INTO Test VALUES (3, 200)
INSERT INTO Test VALUES (4, null)
SET NOCOUNT OFF
SELECT SUM(Value) FROM Test
SELECT SUM(ISNULL(Value,0)) FROM Test
SELECT ISNULL(SUM(Value),0) FROM Test
SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
I'm not saying that the original poster intended this, nor that it's a
good thing. The warning is harmless most of the times and the best way
to avoid it would be to make that column not nullable. I'm only saying
that the ISNULL-s inside the SUM can make a difference.
Razvan|||You are correct in that the NULL inside the column does prevent the warning.
And it is intended -- I want to ensure I get a value back in the event there
was no value. Alternatively, I could turn the warnings off and/or use
ArithAbort and/or ArithIgnore, but I may just go in and prevent this from
occuring :=)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1122211153.821275.237420@.o13g2000cwo.googlegroups.com...
> David Portas wrote:
> They MAY achieve something: if there are NULL-s in that column, the
> ISNULL from inside the SUM prevents the "Warning: Null value is
> eliminated by an aggregate or other SET operation.". For example:
> CREATE TABLE Test (
> ID int primary key,
> Value int NULL
> )
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> SET NOCOUNT ON
> INSERT INTO Test VALUES (1, 100)
> INSERT INTO Test VALUES (2, 100)
> INSERT INTO Test VALUES (3, 200)
> INSERT INTO Test VALUES (4, null)
> SET NOCOUNT OFF
> SELECT SUM(Value) FROM Test
> SELECT SUM(ISNULL(Value,0)) FROM Test
> SELECT ISNULL(SUM(Value),0) FROM Test
> SELECT ISNULL(SUM(ISNULL(Value,0)),0) FROM Test
> I'm not saying that the original poster intended this, nor that it's a
> good thing. The warning is harmless most of the times and the best way
> to avoid it would be to make that column not nullable. I'm only saying
> that the ISNULL-s inside the SUM can make a difference.
> Razvan
>|||Yep, I could remove the SUM and the GROUP BY, thus negating the entire
reason for the query in the first place, but I'm trying to get the whole
enchilada so I can use the summed values in a report. However I believe you
are correct in thinking there are duplicate rows being created -- I'll dig
into that further. Thanks.
"Chandra" <chandra@.discussions.hotmail.com> wrote in message
news:%23Pk9DQDkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> hi
> you might be getting a lot rows from the query and they are getting
> hidden because of the SUM and GROUP BY,
> please try to remove the SUM and GROUP BY and see the duplicate rows.
> once the duplicate rows are eliminated then u can see the expected
> results again
> best Regards,
> Chandra
> http://groups.msn.com/SQLResource/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***|||This is why you guys are MVPs ... that was perfect, giving me precisely the
results I was looking for.
The inner ISNULLS were being used to prevent warnings (as I noted in the
post below), but I am going to clean that up so no NULLS are possible.
Thanks very much for your help.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:mKidneN_kpIx237fRVn-jQ@.giganews.com...
> It looks like SalesId isn't unique in the LaborJobCosts table. Try the
> following query. Notice that RIGHT JOIN is redundant in your original -
> the WHERE clause turns it into an INNER JOIN anyway. I've also taken out
> the ISNULLs from inside the SUM functions - they don't achieve anything
> except possibly slow things down.
> It helps if you include keys and constraints with your DDL and also post
> INSERT statements for some sample data. What you did post tells us that
> the LaborJobCosts doesn't have any key other than the IDENTITY column.
> That's a potentially serious design flaw.
> SELECT SC.itemsalecode,
> ISNULL(SUM(S.totalsaleamount),0) AS grosssales,
> ISNULL(SUM(LC.laborcost),0) AS laborcosts
> FROM Sales S
> JOIN SaleCodes SC
> ON SC.itemsalecode = S.itemsalecode
> LEFT JOIN
> (SELECT salesid, SUM(laborcost) AS laborcost
> FROM LaborJobCosts
> GROUP BY salesid) AS LC
> ON S.salesid = LC.salesid
> WHERE S.completeddate BETWEEN @.beginsaledate AND @.endsaledate
> GROUP BY SC.itemsalecode
> --
> David Portas
> SQL Server MVP
> --
>|||Earl wrote:
> I want to ensure I get a value back in the event there was no value.
As shown by my example, you will get a value even if you DON'T use
ISNULL inside the SUM, as long as you use ISNULL outside the SUM.

> Alternatively, I could turn the warnings off and/or use ArithAbort and/or ArithIgn
ore
Using ArithAbort and/or ArithIgnore will not prevent this warning.
AFAIK, there is no configuration setting to turn off this warning.
Razvan|||> The inner ISNULLS were being used to prevent warnings
Try:
SET ANSI_WARNINGS OFF
David Portas
SQL Server MVP
--

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.
Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>
|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>
|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas

"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.
Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>
|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>
|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas

"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
sql

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL wi
ll
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Additional INSERT, UPDATE, and DELETE Statements Disabled.

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.
I have been looking for an answer to my problem, but unfortunately I couldn't find one. So I created a user here on www.asp.net just for making this post.

Before I continue I just want to apologies if there is another post where this question is already answered.

Please watch this Print Screen I just took: "http://www.bewarmaronsi.com/Capture.JPG "

As you can see the "INSERT, UPDATE, and DELETE Statements" are disabled, and that's exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.

The MDF file is located in the App_data folder and is called ASPNETDB.

And when I try to add custom SQL statements, it gives me Syntax error near "=". Something like that. I bought the Total Training Set1 package and it works perfect in their examples.

I just want to thank you for reading my post and I hope that you got some useful information for me.

By the way, I', from Sweden so you have to excuse me if my English is rusty.

Thanks!

PS: Can it be that I'm running windows Vista?

Try adding a primary key column to the the table and check.

If any issues, let me know.

Sri

|||

Thank you for your postsridhar.av, I don't exactly know what you mean, but I think this might be it… please look at this new print screen:

"

http://www.bewarmaronsi.com/Capture2.JPG

"

And if that's the case, it still doesnt work.

Thanks

|||Right click on the Column -->Set Primary key|||

Thank you so muchsridhar.a, it works perfect now!

I'm a designer so if you need any help in that area, dont hesitate and email me anytime you want.
From Graphics to 3D modeling and Animation.

Thanks!

Additional Information

I found the way to pass a open connection (I had been trying to use an ole
connection instead of a sqql connection) so I think I have eliminated that
as a problem. The stack trace seems to imply that it is not happy with the
parameters. I have them defined as follows:
Dim arParms(3) As SqlParameter
arParms(0) = New SqlParameter("@.id", SqlDbType.Int)
arParms(0).Value = Request.Params("ID")
arParms(1) = New SqlParameter("@.clickcount", SqlDbType.Int)
arParms(1).Direction = ParameterDirection.Output
arParms(2) = New SqlParameter("@.url", SqlDbType.VarChar, 100)
arParms(2).Direction = ParameterDirection.Output
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ux63E$T2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am tyring to use SqlHelper to execute a SP with some passed parameters.
>When I try the statement shown below I get an error "Object reference not
>set to an instance of an object"
> I suspect the connection string as I am not sure exactly what SqlHelper
> wants for that value? I've tried including the provider but it doesn't
> like that. Googling SqlHelper gave me more confusion than help. The
> connection string works fine for normal command use.
> Can anyone point me to some clear information on exactly what the
> SqlHelper command expects in this case? Is there a way to specify an
> existing connection to SqlHelper?
> ===========================
> SqlHelper.ExecuteNonQuery("Data Source = myServer.net; Initial Catalog =
> DB_150547; User ID = myID; Password=myPW", CommandType.StoredProcedure,
> "GetAdClicks", arParms)
>Duihh!!!
The parameter array was Dimed as "3" but there are only 3 elements! The code
was translated from some older "C#" code and they must do it different?
Changed the Dim value to "2" and the problem is solved.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uTUw5VU2FHA.3964@.TK2MSFTNGP10.phx.gbl...
>I found the way to pass a open connection (I had been trying to use an ole
>connection instead of a sqql connection) so I think I have eliminated that
>as a problem. The stack trace seems to imply that it is not happy with the
>parameters. I have them defined as follows:
> Dim arParms(3) As SqlParameter
> arParms(0) = New SqlParameter("@.id", SqlDbType.Int)
> arParms(0).Value = Request.Params("ID")
> arParms(1) = New SqlParameter("@.clickcount", SqlDbType.Int)
> arParms(1).Direction = ParameterDirection.Output
> arParms(2) = New SqlParameter("@.url", SqlDbType.VarChar, 100)
> arParms(2).Direction = ParameterDirection.Output
> Wayne
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:ux63E$T2FHA.400@.TK2MSFTNGP09.phx.gbl...
>

Additional Information

Also, some people do not have middle names, how would I write the query to
pull this out? Thanks again.
"Mike Collins" wrote:

> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick

Additional info

Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.

I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
Bernaridho
For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho
|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho
|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

sql

Additional info

Hi,

I have one client and one server. In both hosts I created an OS-user named User1.

The user have been given various role under the prefix SQLServer2005.

The default instance of SQL Server and instance (background-process) of

Analysis Service are also run under User1.

I connected via Management Studio to Analysis Service directly in the

server. I set the permission to access Analysis Service to Everyone.

This means everyone should be able to use Analysis Service. I have set

ODBC data source to the server. Testing was successful.

I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).

On project setting (created at client) I set the server host as

deployment target. I managed to deploy project when all I have are Data

Source and Data Source Views.

But on deployment I always failed. The errors remain the same, begins with

-

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :

(A) Use a specific username and password

* in which I supply User1 and the password of User1

(B) Use the service account

(C) Use the credentials of the current user

(D) Default

Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?

Thank you,

BernaridhoFor additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

Additional Global fields within RS

Hi,
the usage of GLOBALS in RS is very useful because they could be used
within the definition of data sources as parameter as well as content
to fill report text fields etc.
Has anybody an idea, how to define an additional GLOBAL var and fill it
with a value' (e.g. via code extension)
Thanks for your suggestions
best regards
BruceOne way to do this is to create a parameter and leave the prompt empty... It
will not be shown with the other parameters, but it still part of the
parameters collection...
You might also try to create Statics variables in the REport Code section.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1107776267.070741.229380@.c13g2000cwb.googlegroups.com...
> Hi,
> the usage of GLOBALS in RS is very useful because they could be used
> within the definition of data sources as parameter as well as content
> to fill report text fields etc.
> Has anybody an idea, how to define an additional GLOBAL var and fill it
> with a value' (e.g. via code extension)
> Thanks for your suggestions
> best regards
> Bruce
>

additional data files not filing

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).

We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix?
--
willVerify that the newly added files are in the appropriate file group (probably PRIMARY) and are of the appropriate file type (ie, they were created as data files, not log files). Also, your new file(s) may not get data written to them if the old file still has space available.

Your logic for reducing contention by adding files to match the number of processors does not make sense. Contention is caused by physical IO going to the same physical disk and getting backed up because the pipe to the physical disk is too small. You would add files to a database filegroup to ease contention by creating the files on separate physical disks. It has no relation the number of CPUs.

Regards,

hmscott|||Yes they are all the same file type (data files).

Microsoft claims that you should have a seperate file per processor for high contention OLTP sql servers. Parallelism is the goal here. Sql server can only access 1 file per cpu at any one time. If it was a dual processor machine It would have less impact considering the OS & tempdb usually have something going on in parallel.

Considering we have a quad sql server = 4 files per file group.

As an FYI we are running a raid 10 with 14 disks.|||Hmm, not doubting you, but could you send a link with the MS info? I had not seen that before.

As for your issue, what's the state of the original data file? Full, nearly full or not even close? Also, can you verify that the new files you added were updateable (ie, not read-only)? Beyond that, I would try testing some scenarios out on a development server somewhere.

Regards,

hmscott

Additional Components?

I have a clustered SQL installed but never added the
Replication feature along with the initial setup.
Now I ned to configure replication and thought I could
just go back to the origianl setup files, modify the
installation, add the componenets I need...and I'd be
done. No such luck!
When I go through the setup diologue the option to modify
an existing installation is -not- available and I can't
figure out why.
If someone can help me out I'd sure appreciate it....
TIA,
Jay
After you install a virtual instance of SQL Server on your computer, you cannot add the SQL Server components to your virtual instance of SQL Server by running the Setup
program. To work around this problem, you must remove the virtual instance of SQL Server from your computer and then reinstall the virtual instance of SQL Server by
using a custom installation.
This is documented in Microsoft Knowledge Base article
You cannot add SQL Server components to an existing virtual instance of SQL Server by running the Setup program
http://support.microsoft.com/?kbid=867650
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks Uttam!!
How is RRE life treating you...?

>--Original Message--
>After you install a virtual instance of SQL Server on
your computer, you cannot add the SQL Server components to
your virtual instance of SQL Server by running the Setup
>program. To work around this problem, you must remove
the virtual instance of SQL Server from your computer and
then reinstall the virtual instance of SQL Server by
>using a custom installation.
>This is documented in Microsoft Knowledge Base article
>You cannot add SQL Server components to an existing
virtual instance of SQL Server by running the Setup program
>http://support.microsoft.com/?kbid=867650
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
>http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
way
>to do this is to visit the following websites:
http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>
|||You are most welcome.
Life is good -- learning something new everyday.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Additional articles added ..

I was going through this link and had a question with regards to caveats
when new articles are added
The link is http://www.replicationanswers.com/No...alizations.asp
So Say i have nosync initially setup and everything is going on fine. I now
want to add new articles to the publication.
Can i not just do an sp_addarticle followed by a sp_addsubscription with
@.sync_type='automatic' and then run the snapshot agent.
Will that not apply the snapshot for just these new articles ?
Using Transactional Replication
Thanks
Yes, you're quite right. Section 2.1 is referring to the gui, but it is
possible to mix noinit and automatic in the same publication by scripts.
I've put this in as an interesting aside (attributed to yourself ),
although I maintain that this could lead to unmaintainable publications.
Rgds,
Paul Ibison
sql

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..