Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
ReshmaHi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/d...r />
_2z4x.asp
http://www.microsoft.com/sql/evalua...es/distpart.asp
http://msdn.microsoft.com/library/d...r />
_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

Distributed databases

Hello,
I am new to distributed databases. I wanted to use stored
procedures in distributed databases.
If a view contains query in which tables from different servers are used,
how should I reference them. For example
Suppose me view is
CREATE VIEW dbo.Example1
AS
SELECT * from table1, tabel2
If now table1 is present on 1 server and table2 on other. Can I pass a
argument to the view saying from which server should I take a particular
table from.
Also when I try to execute a stored procedure, it gives followinh error
Server [servername] is not configured for RPC
Can anynone help me solve this out
Thanks,
Reshma
Hi
Look up 'Partitioned View's, 'Distributed Partitioned Views' and 'Linked
Servers'.
Some links:
http://msdn.microsoft.com/library/de...qd_10_2z4x.asp
http://www.microsoft.com/sql/evaluat...s/distpart.asp
http://msdn.microsoft.com/library/de...qd_12_4lpv.asp
What you are describing is really linked servers, but it does have
performance implications as remote queries have to be executed and data
passed around. Partitioning data is only useful when you have massive
tables.
When you create the view, you have to know the tables and locations. You can
not dynamically decide where to get the data from.
p.s. Posting a question to one newsgroup will do the job.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Reshma Prabhu" <reshma_prabhu@.persistent.co.in> wrote in message
news:O#Uj4QFLFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am new to distributed databases. I wanted to use stored
> procedures in distributed databases.
> If a view contains query in which tables from different servers are used,
> how should I reference them. For example
> Suppose me view is
>
> CREATE VIEW dbo.Example1
> AS
> SELECT * from table1, tabel2
> If now table1 is present on 1 server and table2 on other. Can I pass a
> argument to the view saying from which server should I take a particular
> table from.
> Also when I try to execute a stored procedure, it gives followinh error
> Server [servername] is not configured for RPC
>
> Can anynone help me solve this out
> Thanks,
> Reshma
>

distinct count

Hi

I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.

Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).

Can some body help?

Thanks in Advance!

Santhosh

Select

Employee.EmployeeName,

Employee.EmployeeNo,

|||

Select EmployeeName, EmployeeNo, Count(*) As NoOfDays

From EmployeeShift

Where Month = @.Month And Year = @.Year

Group By EmployeeName, EmployeeNo

|||

You could create a view as

Select EmployeeName, EmployeeNo, Month, Year, Count(*) As NoOfDays

From EmployeeShift

Group By EmployeeName, EmployeeNo, Month, Year

and apply Where to it...

sql

Displaying sub report even when it has no data

Hi,

I have a main report with a sub report in it. Sub Report contains 1 table and some labels. I am passing 1 parameter from the main report to sub report on the basis of which sub report is generated with in the main report. In case if there comes some records in the sub report than the table as well as the other labels of the sub report are coming properly. But if no records are there in the sub report than it doesn't display the complete sub report i.e. no table and no labels which are with in the sub report. I want to make sure that labels should come even if there is no data in the sub report.

I had this same problem. The solution I came up with was to modify my stored procedure to always returnsome value.

SELECT @.Count =Count(*)FROM TableWHERE parameter1 = @.parameter1AND parameter2 = @.parameter2IF @.Count > 0BEGINSELECT Field1, Field2, Field3,DummyValue = -1FROM TableWHERE parameter1 = @.parameter1AND parameter2 = @.parameter2ORDER BY Field1DESC, Field2DESC, Field3ENDELSEBEGINSELECT DummyValue = @.CountEND

I don't know if this is best solution, but it definitely works. Hope this helps.

Displaying image from db in crystal report

hello there

I am using VB.Net 2003 with oracle 9i. My table contains a BLOB field that contains images. I tried to create a report (crystal report that is packed with VB.Net 2003) by including the table and dragging the BLOB field onto the report. But when i try to run the report, it gives me "Failed to open a rowset" error. If i run the report without the BLOB field, the report works fine.

Any idea?!?!

Thanks in advance
AndyHow did you store the images in the table?
Sounds that some images are corrupted
Visist this site and see if you find solution
www.businessobjects.com

Displaying HTML as text

I am trying to display a field in a report that sometimes contains html
stored as text. I want to be able to strip out the html tags so I can display
the text only in the report.To strip off the html I believe there is a framework function that you could
use. Set the value of the textbox to an expression like this:
= Code.StripHTML(Fields!Fieldname.value)
You would write the function StripHTML that would return the value with the
html stripped.
This link shows how to do this using regular expressions.
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
There is definitely a dotnet function that you could use too. Google on
stripping html and you should be able to find it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
>I am trying to display a field in a report that sometimes contains html
> stored as text. I want to be able to strip out the html tags so I can
> display
> the text only in the report.
>|||Thanks for the info Bruce. I am not a programmer so forgive for my questions,
how do you embed custom code into a sql report?
"Bruce L-C [MVP]" wrote:
> To strip off the html I believe there is a framework function that you could
> use. Set the value of the textbox to an expression like this:
> = Code.StripHTML(Fields!Fieldname.value)
> You would write the function StripHTML that would return the value with the
> html stripped.
> This link shows how to do this using regular expressions.
> http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
> There is definitely a dotnet function that you could use too. Google on
> stripping html and you should be able to find it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael" <Michael@.discussions.microsoft.com> wrote in message
> news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
> >I am trying to display a field in a report that sometimes contains html
> > stored as text. I want to be able to strip out the html tags so I can
> > display
> > the text only in the report.
> >
>
>

Displaying data on a new page

Hi,
I am trying to display data on a new page when one field contains
specific data.
Once this data is read I wish for any following information to be
displayed on the next page. Any help would be much appreciated.
IvanHere is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Note in your case you would use Fields!Fieldname.value instead. Also, if you
want html just leave off the format and encoding part. RS defaults to HTML.
You need at least RS 2000 SP1 or greater for the above to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ivan" <ivan.drew@.gmail.com> wrote in message
news:1141128116.719467.278170@.v46g2000cwv.googlegroups.com...
> Hi,
> I am trying to display data on a new page when one field contains
> specific data.
> Once this data is read I wish for any following information to be
> displayed on the next page. Any help would be much appreciated.
> Ivan
>

Displaying a List Horizontally

Hi All,

I'm new to reporting services and I need some help. I've created a list which contains a photo and user name. Currently, it displays all the information I want however, the list expands down the page in one column. I would like it to expand across the page horizontally (4 columns) and then skip down to the next line. Can someone tell me how to do this using report designer? Thank youReporting Services doesn't support listing horizontally natively.

You could workaround by trying something like the following (conceptually this is easier to understand):
1) inserting a constructed column in your dataset (right click on your fields windows and select add)
2) set the value of the constructed column so it increases by 1 for every 4th value of RowNumber()... maybe =Floor(RowNumber()/4)
3) Add another constructed column in your data set
4) set the value to be the remainder =Mod(RowNumber()/4) this should give values from 1 - 4.
5) add a grouping to your list based on the constructed column created in #2
6) Add 4 lists into your list. Let's call these "inner lists"
7) for each inner list, add a filter on for the appropriate value of the constructed column created in #3.
8) in each inner list display your data the way you want it to look

(take a coffee break cause this was hard :-))

You might also be able to do something similar with a Matrix control where you put the constructed column in #4 as the column grouping and the constructed column in #2 as the row grouping. A little harder to understand maybe but probably easier to maintain in the long run.

Good luck,
-Lukasz|||I'm trying to use the solution provided by lukasz but I seem to be getting an error when creating the constructed field as suggested:

A sort expression for the field ‘=RowNumber("table")’ uses the function RowNumber. RowNumber cannot be used in sort expressions.

Anyone have any idea why?

Thanks.

|||

Hello,

This does not work for me as well. I get the same error as described by Pumm3l.

Can someone please provide a reliable answer?

Thanks.

Displaying a List Horizontally

Hi All,

I'm new to reporting services and I need some help. I've created a list which contains a photo and user name. Currently, it displays all the information I want however, the list expands down the page in one column. I would like it to expand across the page horizontally (4 columns) and then skip down to the next line. Can someone tell me how to do this using report designer? Thank youReporting Services doesn't support listing horizontally natively.

You could workaround by trying something like the following (conceptually this is easier to understand):
1) inserting a constructed column in your dataset (right click on your fields windows and select add)
2) set the value of the constructed column so it increases by 1 for every 4th value of RowNumber()... maybe =Floor(RowNumber()/4)
3) Add another constructed column in your data set
4) set the value to be the remainder =Mod(RowNumber()/4) this should give values from 1 - 4.
5) add a grouping to your list based on the constructed column created in #2
6) Add 4 lists into your list. Let's call these "inner lists"
7) for each inner list, add a filter on for the appropriate value of the constructed column created in #3.
8) in each inner list display your data the way you want it to look

(take a coffee break cause this was hard :-))

You might also be able to do something similar with a Matrix control where you put the constructed column in #4 as the column grouping and the constructed column in #2 as the row grouping. A little harder to understand maybe but probably easier to maintain in the long run.

Good luck,
-Lukasz|||I'm trying to use the solution provided by lukasz but I seem to be getting an error when creating the constructed field as suggested:

A sort expression for the field ‘=RowNumber("table")’ uses the function RowNumber. RowNumber cannot be used in sort expressions.

Anyone have any idea why?

Thanks.

|||

Hello,

This does not work for me as well. I get the same error as described by Pumm3l.

Can someone please provide a reliable answer?

Thanks.

Display over 256 characters for VARCHAR(3500) field

Hopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.

How do I go about extracting ALL of the data from this field? Any help is much appreciated.

Thanks in advance.In Query Analyzer, select Options from the menu and bump up the maximum output characters, which defaults to 256.