3 questions

Hi -- I'm new to SQL and thus also new to SQL Server.
I'm working on a project that has data stored in 4 databases. I want to
create a 5th database for my work. I will need to retrieve data from those
other 4 databases -- usually creating tables in my 5th database. I want to
put together stored procedures to do this. I can extract data and create
tables within one database, but have no idea how to reach out to a different
one.
Eventually, in this project I will need to read the records, in order and
create a running calculation -- not a running sum. I can do this within VB
pretty easily, but I don't know if this can be done is a query. The
calculation is: (field 2, record n) =
(field 1, record n) * (1 - field 2, record n-1) + (field2,record n)
Finally, can anyone suggest a good book that might help me with some of
these types of questions?
I'd appreciate any help on any portion of the above!
Thanks,
ArtArt,
To access the tables in the other databases (assuming the same server) just
qualify the object using DATABASE.OWNER.OBJECT i.e., pubs.dbo.authors.
Books (SQL):
The Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
Professional SQL Server 2000 Programming
by Robert Vieira
http://www.amazon.com/exec/obidos/t...=glance&s=books
Joe Celko's SQL for Smarties (2005 ed)
by Joe Celko
http://www.amazon.com/exec/obidos/t...=books&n=507846
HTH
Jerry
"Art" <Art@.discussions.microsoft.com> wrote in message
news:D5386C63-4FEA-42A6-8958-A2CF67FCB726@.microsoft.com...
> Hi -- I'm new to SQL and thus also new to SQL Server.
> I'm working on a project that has data stored in 4 databases. I want to
> create a 5th database for my work. I will need to retrieve data from
> those
> other 4 databases -- usually creating tables in my 5th database. I want
> to
> put together stored procedures to do this. I can extract data and create
> tables within one database, but have no idea how to reach out to a
> different
> one.
> Eventually, in this project I will need to read the records, in order and
> create a running calculation -- not a running sum. I can do this within
> VB
> pretty easily, but I don't know if this can be done is a query. The
> calculation is: (field 2, record n) =
> (field 1, record n) * (1 - field 2, record n-1) + (field2,record n)
> Finally, can anyone suggest a good book that might help me with some of
> these types of questions?
> I'd appreciate any help on any portion of the above!
> Thanks,
> Art|||Jerry,
Thanks very much! For both answers.
Art
"Jerry Spivey" wrote:

> Art,
> To access the tables in the other databases (assuming the same server) jus
t
> qualify the object using DATABASE.OWNER.OBJECT i.e., pubs.dbo.authors.
> Books (SQL):
> The Guru's Guide to Transact-SQL
> by Ken Henderson
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Professional SQL Server 2000 Programming
> by Robert Vieira
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Joe Celko's SQL for Smarties (2005 ed)
> by Joe Celko
> http://www.amazon.com/exec/obidos/t...=books&n=507846
> HTH
> Jerry
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:D5386C63-4FEA-42A6-8958-A2CF67FCB726@.microsoft.com...
>
>|||>> I can extract data and create tables within one database, but have no
Use 3-part naming like database.owner.object
Perhaps if you post the DDLs, sample data & expected results, others could
give it a try. ( www.aspfaq.com/5006 ). In some cases, using a client side
programming language might be the option. In other cases, you might have to
use some looping construct like a cursor.
SQL Server Books Online should give you most of the basics on t-SQL.
Anith|||Thanks Anith,
I had been using a programming language, but was wondering if I might be
better off using SQL (assuming it's possible). Thanks again.
Art
"Anith Sen" wrote:

> Use 3-part naming like database.owner.object
>
> Perhaps if you post the DDLs, sample data & expected results, others could
> give it a try. ( www.aspfaq.com/5006 ). In some cases, using a client side
> programming language might be the option. In other cases, you might have t
o
> use some looping construct like a cursor.
>
> SQL Server Books Online should give you most of the basics on t-SQL.
> --
> Anith
>
>|||Art, you are in trouble. Let's get back to the basics of an RDBMS.
Rows are not records; fields are not columns; tables are not files;
your post is nonsense. But it tells us that you have no idea about
RDBMS, so your design is probably screw to hell and breakfast.
You seem to be doing attribute splitting on databases; Otherwise, it
would not make sense to mix them.|||"But it tells us that you have no idea about RDBMS"
Hey --CELKO--, keep your posts focused on the issue at hand, not the person
posting. The forum is here to help each other, not to put down people.
This a constant problem with you. Your input is valuable and appreciated,
but these kind of comments are not (at least by me).
Are there no moderators on this site?
"--CELKO--" wrote:

> Art, you are in trouble. Let's get back to the basics of an RDBMS.
> Rows are not records; fields are not columns; tables are not files;
> your post is nonsense. But it tells us that you have no idea about
> RDBMS, so your design is probably screw to hell and breakfast.
> You seem to be doing attribute splitting on databases; Otherwise, it
> would not make sense to mix them.
>

2005 Reporting Srv cannot connect to Oracle datasource on web server

created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection)
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)

any thoughts or direction greatly appreciated

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please read the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

|||

Robert,

Thanks for the help, this did in fact solve the issue (once the permissions were granted for the entire Oracle directory). Why would such a helpful article not show up in my searches through the help maze? Is there another entry point to search for these articles other than the help search engine?

thanks again,

Richard

|||

Wow this really helped me out also, had the exact same problem. Thanks. I too had to set the permissions for the entire Oracle directory for this to work. Also take note of which account your installation is using, mine wasn't using the Network Service account but instead a special service account created by the system admins, I had to set the permissions for that account on the folders.

Dave

2005 Reporting Srv cannot connect to Oracle datasource on web server

created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection)
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)

any thoughts or direction greatly appreciated

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please read the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

|||

Robert,

Thanks for the help, this did in fact solve the issue (once the permissions were granted for the entire Oracle directory). Why would such a helpful article not show up in my searches through the help maze? Is there another entry point to search for these articles other than the help search engine?

thanks again,

Richard

|||

Wow this really helped me out also, had the exact same problem. Thanks. I too had to set the permissions for the entire Oracle directory for this to work. Also take note of which account your installation is using, mine wasn't using the Network Service account but instead a special service account created by the system admins, I had to set the permissions for that account on the folders.

Dave

2005 Reporting service - change commandtext runtime from .net

Hi
I am new to SSRS and have some trouble.
I have a report which is deployed and I can show this report
in a project ( asp.net Framework vs. 2 ) using
Microsoft.Reporting.WebForms.ReportViewer.
Thats fine, but now I have to make up some code for changing the
Query used in my datasets:
I need to get my sql, and modify it ( put on some "Where... ", "exists()",
and so on ) - then put the sql back in the report and then execute/show the
report.
I am familiar with parameters, but parameters can't cover my needs.
I've googled and found a lot of stuff, but I can't find any examples - maybe
it is because I don't know which words to use, or how to filter the
information
Hope somebody can help
Thanks
ChristinaI don't believe you can do that when the ReportViewer is in "Remote" mode
but in "Local" mode you are the one that supplies the DataSource to report
so you can execute whatever queries you want and then pass the DataSource
into the report by doing ReportViewer.LocalReport.DataSources.Add.
You can find a lot of good examples and information for ReportViewer here:
http://www.gotreportviewer.com/
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Chrisser" <cbj@.egdatainform.dk>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: 2005 Reporting service - change commandtext runtime from .net
> Date: Thu, 4 Oct 2007 13:44:22 +0200
> Hi
> I am new to SSRS and have some trouble.
> I have a report which is deployed and I can show this report
> in a project ( asp.net Framework vs. 2 ) using
> Microsoft.Reporting.WebForms.ReportViewer.
> Thats fine, but now I have to make up some code for changing the
> Query used in my datasets:
> I need to get my sql, and modify it ( put on some "Where... ", "exists()",
> and so on ) - then put the sql back in the report and then execute/show
the
> report.
> I am familiar with parameters, but parameters can't cover my needs.
> I've googled and found a lot of stuff, but I can't find any examples -
maybe
> it is because I don't know which words to use, or how to filter the
> information
> Hope somebody can help
> Thanks
> Christina
>
>|||Chris Alton [MSFT] wrote:
> I don't believe you can do that when the ReportViewer is in "Remote"
> mode but in "Local" mode you are the one that supplies the DataSource
> to report so you can execute whatever queries you want and then pass
> the DataSource into the report by doing
> ReportViewer.LocalReport.DataSources.Add.
> You can find a lot of good examples and information for ReportViewer
> here: http://www.gotreportviewer.com/
hmm, but thanks anyway :-)
Christina

2005 Express with VS 2003?

I am in the happy position of starting a new project from scratch. I have
only VS 2003, do not want to dabble in VS 2005 Beta yet, but am keen to plan
for using SQL Server Express 2005, which would be delivered to the (small)
customer.
Can I use 2005 Express with VS 2003 (and still maintain old code against SQL
Server 2000)?
If not, what does anyone advise - stick to SQL 2000? - and if so, will the
code changes for Express 2005 be minimal?
Many thanks
James StewartIf this is a new project from scratch then what better time to start with
VS2005? In any case most things that you do for MSDE will still work in
Express but if you were to go with Express to start you can not use any new
2005 features if you need backwards compatibility.
Andrew J. Kelly SQL MVP
"jas" <jas@.discussions.microsoft.com> wrote in message
news:A95C5622-B38F-4D35-BBDB-8A1CEE0D907C@.microsoft.com...
>I am in the happy position of starting a new project from scratch. I have
> only VS 2003, do not want to dabble in VS 2005 Beta yet, but am keen to
> plan
> for using SQL Server Express 2005, which would be delivered to the (small)
> customer.
> Can I use 2005 Express with VS 2003 (and still maintain old code against
> SQL
> Server 2000)?
> If not, what does anyone advise - stick to SQL 2000? - and if so, will the
> code changes for Express 2005 be minimal?
> Many thanks
> James Stewart
>|||Well, I'd love to, but now is not quite the time for risking Beta s/w nor fo
r
installing 2005 alongside VS 2003 on my laptop: too much too soon too close
together too busy...
So will it be ok to install Express 2005 with VS 2003 (AND SQL Server 2000)
on the same machine, so that I can try out Express 2000,
- or -
Should I just continue (with known stability) with VS 2003 + SQL Server 2000
knowing that the conversions to a) Express 2005 and then b) VS 2005 should
not be painful?
Of course, if I knew when (as an MSDN subscriber) full release versions of
VS 2005 and Express 2005 were coming out, I might take the plunge...
Thanks
James S.
"Andrew J. Kelly" wrote:

> If this is a new project from scratch then what better time to start with
> VS2005? In any case most things that you do for MSDE will still work in
> Express but if you were to go with Express to start you can not use any ne
w
> 2005 features if you need backwards compatibility.
> --
> Andrew J. Kelly SQL MVP
>
> "jas" <jas@.discussions.microsoft.com> wrote in message
> news:A95C5622-B38F-4D35-BBDB-8A1CEE0D907C@.microsoft.com...
>
>|||> Of course, if I knew when (as an MSDN subscriber) full release versions of
> VS 2005 and Express 2005 were coming out, I might take the plunge...
From Paul Flessner's keynote at TechEd 2005:
<Excerpt
href="http://www.microsoft.com/presspass/press/2005/jun05/TechEd2005Day2PR.m
spx">
As part of the keynote address, Paul Flessner, senior vice president of
Server Applications at Microsoft, showed the company's continued momentum in
preparation for the launch of SQL ServerT 2005, Visual Studio 2005 and
BizTalk Server 2006, and announced that these products will be formally
launched during the w of Nov. 7.
</Excerpt>
Note that installing beta software on you primary development box is at your
own risk. If you want to go for the 2005 beta stuff, you should consider an
isolated environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"jas" <jas@.discussions.microsoft.com> wrote in message
news:BB286E29-3EA6-492E-85B8-DC9175C07D04@.microsoft.com...
> Well, I'd love to, but now is not quite the time for risking Beta s/w nor
> for
> installing 2005 alongside VS 2003 on my laptop: too much too soon too
> close
> together too busy...
> So will it be ok to install Express 2005 with VS 2003 (AND SQL Server
> 2000)
> on the same machine, so that I can try out Express 2000,
> - or -
> Should I just continue (with known stability) with VS 2003 + SQL Server
> 2000
> knowing that the conversions to a) Express 2005 and then b) VS 2005 should
> not be painful?
> Of course, if I knew when (as an MSDN subscriber) full release versions of
> VS 2005 and Express 2005 were coming out, I might take the plunge...
> Thanks
> James S.
> "Andrew J. Kelly" wrote:
>|||As Dan mentioned the date for the launch is Nov 7th. SQL Server Express is
still Beta since it is SQL2005. If you don't want to risk side by side then
don't do it. You can load it in a virtual PC so it won't harm anything
else.
Andrew J. Kelly SQL MVP
"jas" <jas@.discussions.microsoft.com> wrote in message
news:BB286E29-3EA6-492E-85B8-DC9175C07D04@.microsoft.com...
> Well, I'd love to, but now is not quite the time for risking Beta s/w nor
> for
> installing 2005 alongside VS 2003 on my laptop: too much too soon too
> close
> together too busy...
> So will it be ok to install Express 2005 with VS 2003 (AND SQL Server
> 2000)
> on the same machine, so that I can try out Express 2000,
> - or -
> Should I just continue (with known stability) with VS 2003 + SQL Server
> 2000
> knowing that the conversions to a) Express 2005 and then b) VS 2005 should
> not be painful?
> Of course, if I knew when (as an MSDN subscriber) full release versions of
> VS 2005 and Express 2005 were coming out, I might take the plunge...
> Thanks
> James S.
> "Andrew J. Kelly" wrote:
>