OPENQUERY ISSUE

I am trying to pass a variable to an openquery stmt within a proc. The
following worked a couple of times, but I am receiving the error below now ?
Any suggestions on a method to pass the variable into the OPENQUERY Statemen
t
?
CREATE PROCEDURE usp_GetData (@.REQUEST_ID as varchar(10))
AS
declare @.RESULT varchar(20)
Declare c Cursor For
SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM oracle_view
WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Open C
Fetch c into @.request_ID, @.RESULT -- Get First record data
While @.@.FETCH_STATUS = 0
Begin
INSERT INTO RESULTS (REQUEST_ID, RESULT)
VALUES (@.request_ID, @.RESULT)
Fetch c into @.request_ID, @.RESULT -- Get Next Record
End
Close C
Deallocate C
GO
-- Error received.
Server: Msg 7330, Level 16, State 2, Procedure usp_GetRequestID_Data, Line 2
2
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01722: invalid number> SELECT * FROM OPENQUERY(ORACLE_4,'SELECT REQUEST_ID, RESULT FROM
> oracle_view WHERE REQUEST_ID=''+@.REQUEST_ID+''')
Usually it helps to manually evaluate the expression you are trying to execu
te.
DECLARE @.TEST VARCHAR(200)
SET @.TEST = 'SELECT REQUEST_ID, RESULT FROM oracle_view WHERE REQUEST_ID=''+
@.REQUEST_ID+'''
SELECT @.TEST|||Hi,
as far as I know, OPENQUERY cannot accept variable.
normally what I do is to create a dynamic sql string first then only execute
it.
In BOL, you should be able to find something like this:
--
OPENQUERY
Executes the specified pass-through query on the given linked server, which
is an OLE DB data source. The OPENQUERY function can be referenced in the
FROM clause of a query as though it is a table name. The OPENQUERY function
can also be referenced as the target table of an INSERT, UPDATE, or DELETE
statement, subject to the capabilities of the OLE DB provider. Although the
query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
Arguments
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
Remarks
OPENQUERY does not accept variables for its arguments.
--
As you can see, variable is not allowed. I am not so sure about SQL Server
2005.
I get this from SQL Server 2000 BOL.
hope this will help.
Leo

Open-ended querying of DB (??)

I am working on a project with a couple of hundred tables and views. There is one primary table around which all other tables revolve with the primary table having somewhere around 42 to 45 million records. Some of the related tables can have many times this number of records. The data is static in that it only changes at specific intervals (once a month or so) when more records are added and some "old" records are removed.

My problem is that users need to query these tables without any major restrictions. I mean, the front end will allow users to create almost any selection criteria form the many columns in the DB. The selection of values will be controlled via drop-downs and lists from where the user will select the criteria. The other consideration is that the grouping can be for any value and up to ten levels deep. Our users are not sophisticated enough to create their own SQL-like queries so we have to guide them through the process of selecting the values…

I am trying to create stored procedures that will allow this behavior but I continue to run into the problem of how to properly join the tables when the combinations are almost endless. My conclusion is that although it seems this seems to be a case where D-SQL is a good choice, there are some draw-backs I am not sure we can afford, specifically the security issues. Furthermore, the creation of a D-SQL "code-generator" seems like almost impossible task and time is of the essence.

Has anyone here had any experience with a similar situation that can shed some light?I think without dynamic SQL, you're stuck with creating a view for every possible combination of joins - not really an option. I think you'll need to use it. Is it possible to provide the highest priority querying and slowly add more tables as you have a chance to build it? If a user isn't sophisticated enough to build SQL, will they be aware enough to even understand what joining tables does for them?

I've done 'build-your-own' query-generating stuff before, but never to that scope. That could be a tough one.|||A Friend of mine was reading an article in visualstudiomagazine.com that might be of some assistance to you. Check out the online version http://www.fawcette.com/vsm/2004_10/magazine/columns/databasedesign/

Hth,|||Scott, thanks for the heads-up. I will post my impressions on the article later on.|||PD, I have no choice but to look for a solution. Our users currently have a system that is based on a "flat-file" format where creating the queries to produce the reports is fairly easy. When we decided to re-write the system as a web based application, the decision was made to move to SQL Server and take advantage of its power. We normalized the files, added records for a wider time frame and have created a beautiful data structure. Unfortunately now we are confronted with the issues of querying the data in a multitude of ways.

We are considering de-normalizing the data a little, using Analysis Services, English Queries, and others. Given the confidential nature of the data, security is a must and using D-SQL may be a problem. We can live with a system where the user makes his selection from lists and drop-downs and we construct the query behind the scenes; we do not expect them to know SQL.|||Beleive it or not there are actual tools out there that exist to perform this task. Mind you some may seem expensive... but they work, and the amount of development time saved to get something that works and may be useful in other areas in your organization may warrant the costs.

The tools fall into the Business Intelligence area of software, and are typically called EII or something to that nature. EII stands for Enterprise Information Integration.

Another way to explain it, you need a tool, typically made to fetch data from multiple sources, apply english names to them, then you specify how the individual "views of data" could be related to another. Next the tool gives you a GUI interface and/or exposes an API allowing you to say "I want data from 'a' and 'b' filtered on 'c' and grouped on 'd'."

Nimble Technologies made such a tool. My company, Actuate Corporation, has acquired them to use their technology within our own software. I'm not sure if we did an outright takeover, or just bought largely into their company or what, but Nimble may or may not exist. In either case, they had competitors, so the tools do exists out there.

I would suggest looking up Business Intelligence tools, or publications that frequently discuss such software. Publications geared towards CIOs and IT management would be another area to look.

open up .mdf and .ldf file types

I have a couple of these file types and i need to open them to look at the
databses. These were sent to me form a different company and my company does
not have ms sql server. Is there any way i can open these files with out
havign to buy any product. All i need to do is look at them nothign else.
PaulYou could grab the 120-day trial edition from here
http://www.microsoft.com/sql/evaluation/trial/default.asp, and use that if
this is only a temporary need. The database format is proprietary and I'm
not aware of any software that will read them for you outside of SQL Server.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in message
news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
>I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul|||You could also use MSDE. It comes free with MS Office but you have to
install it seperately and it will only handle database up to 2 GB or less.
Take a look for the MSDE folder on the MS Office distribution CD.
Sincerely,
Anthony Thomas
"NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in message
news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
I have a couple of these file types and i need to open them to look at the
databses. These were sent to me form a different company and my company
does
not have ms sql server. Is there any way i can open these files with out
havign to buy any product. All i need to do is look at them nothign else.
Paul|||I didn't recommend MSDE because it doesn't come with a GUI interface. If
someone isn't use to working with SQL Server, that can be a drawback. :-)
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:e$pnccS3EHA.3716@.TK2MSFTNGP10.phx.gbl...
> You could also use MSDE. It comes free with MS Office but you have to
> install it seperately and it will only handle database up to 2 GB or less.
> Take a look for the MSDE folder on the MS Office distribution CD.
> Sincerely,
>
> Anthony Thomas
>
> --
> "NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in
> message
> news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
> I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul
>|||Ah, but it does if they are pulling MSDE from the Office CDs: MS Access
projects!
:-)
Sincerely,
Anthony Thomas
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:41b72452$1@.news.microsoft.com...
I didn't recommend MSDE because it doesn't come with a GUI interface. If
someone isn't use to working with SQL Server, that can be a drawback. :-)
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:e$pnccS3EHA.3716@.TK2MSFTNGP10.phx.gbl...
> You could also use MSDE. It comes free with MS Office but you have to
> install it seperately and it will only handle database up to 2 GB or less.
> Take a look for the MSDE folder on the MS Office distribution CD.
> Sincerely,
>
> Anthony Thomas
>
> --
> "NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in
> message
> news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
> I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul
>

open up .mdf and .ldf file types

I have a couple of these file types and i need to open them to look at the
databses. These were sent to me form a different company and my company does
not have ms sql server. Is there any way i can open these files with out
havign to buy any product. All i need to do is look at them nothign else.
Paul
You could grab the 120-day trial edition from here
http://www.microsoft.com/sql/evaluat...l/default.asp, and use that if
this is only a temporary need. The database format is proprietary and I'm
not aware of any software that will read them for you outside of SQL Server.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in message
news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
>I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul
|||You could also use MSDE. It comes free with MS Office but you have to
install it seperately and it will only handle database up to 2 GB or less.
Take a look for the MSDE folder on the MS Office distribution CD.
Sincerely,
Anthony Thomas

"NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in message
news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
I have a couple of these file types and i need to open them to look at the
databses. These were sent to me form a different company and my company
does
not have ms sql server. Is there any way i can open these files with out
havign to buy any product. All i need to do is look at them nothign else.
Paul
|||I didn't recommend MSDE because it doesn't come with a GUI interface. If
someone isn't use to working with SQL Server, that can be a drawback. :-)
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:e$pnccS3EHA.3716@.TK2MSFTNGP10.phx.gbl...
> You could also use MSDE. It comes free with MS Office but you have to
> install it seperately and it will only handle database up to 2 GB or less.
> Take a look for the MSDE folder on the MS Office distribution CD.
> Sincerely,
>
> Anthony Thomas
>
> --
> "NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in
> message
> news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
> I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul
>
|||Ah, but it does if they are pulling MSDE from the Office CDs: MS Access
projects!
:-)
Sincerely,
Anthony Thomas

"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:41b72452$1@.news.microsoft.com...
I didn't recommend MSDE because it doesn't come with a GUI interface. If
someone isn't use to working with SQL Server, that can be a drawback. :-)
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:e$pnccS3EHA.3716@.TK2MSFTNGP10.phx.gbl...
> You could also use MSDE. It comes free with MS Office but you have to
> install it seperately and it will only handle database up to 2 GB or less.
> Take a look for the MSDE folder on the MS Office distribution CD.
> Sincerely,
>
> Anthony Thomas
>
> --
> "NeedSimpleHelp" <NeedSimpleHelp@.discussions.microsoft.com> wrote in
> message
> news:EE5D20CA-BF43-4B76-AC85-F0014B71431E@.microsoft.com...
> I have a couple of these file types and i need to open them to look at the
> databses. These were sent to me form a different company and my company
> does
> not have ms sql server. Is there any way i can open these files with out
> havign to buy any product. All i need to do is look at them nothign else.
> Paul
>

Only the administrator can open the reports

Hi,
I've been struggling with this problem for a couple of days. The RS system
seems to be working fine. However only the administrators can open the
reports. If a user attempts to open a report, a login window pops up.
After pressing OK three times, a message "You are not authorized to view
this page" is displayed. Any ideas on how to fix this? Thanks.
Fabian CevallosMake sure you have given the users permissions to view the report (Browser is
the default access level).
SQLRS Server requires you to authenticate users of a domain. Those admins
that have full permissions on the server itself will have no problems, but
until you 'activate' accounts, all other domain accounts are unknow - and
therefore unauthorized - to access any reports at all.
Now comes the crunch. SQLRS will allow you to create access for domain
accounts. Unfortunately I have not -as yet - found a way to create local
group acconus per se. Therefore, if you want a set number of managers to view
a report, you may need to either set the group up on the domain or manage
them individually on the SQLRS server.
"Fabian Cevallos" wrote:
> Hi,
>
> I've been struggling with this problem for a couple of days. The RS system
> seems to be working fine. However only the administrators can open the
> reports. If a user attempts to open a report, a login window pops up.
> After pressing OK three times, a message "You are not authorized to view
> this page" is displayed. Any ideas on how to fix this? Thanks.
>
> Fabian Cevallos
>
>
>|||The users had permissions to view the report... I ended up removing SQLRS
and installing it again. Now everything works!
"Logicalman" <Logicalman@.discussions.microsoft.com> wrote in message
news:20743E32-2494-4FDE-9ED7-0A34A3A94FD3@.microsoft.com...
> Make sure you have given the users permissions to view the report (Browser
> is
> the default access level).
> SQLRS Server requires you to authenticate users of a domain. Those admins
> that have full permissions on the server itself will have no problems, but
> until you 'activate' accounts, all other domain accounts are unknow - and
> therefore unauthorized - to access any reports at all.
> Now comes the crunch. SQLRS will allow you to create access for domain
> accounts. Unfortunately I have not -as yet - found a way to create local
> group acconus per se. Therefore, if you want a set number of managers to
> view
> a report, you may need to either set the group up on the domain or manage
> them individually on the SQLRS server.
> "Fabian Cevallos" wrote:
>> Hi,
>>
>> I've been struggling with this problem for a couple of days. The RS
>> system
>> seems to be working fine. However only the administrators can open the
>> reports. If a user attempts to open a report, a login window pops up.
>> After pressing OK three times, a message "You are not authorized to view
>> this page" is displayed. Any ideas on how to fix this? Thanks.
>>
>> Fabian Cevallos
>>
>>