OpenQuery and passing variables

Anyone,

Is this possible?

I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuery
statement. I need to pass a list of ever-changing deal numbers My
list of numbers are stored as a table on MS SQL.

So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
>From Teradata_Table_1
Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?

Anyone can help?

Doug(douglascfast@.hotmail.com) writes:
> So what I want is this
> Select * from OpenQuery(TeraSrvr, "
> Select Col1
> , Col2
> , Col3
>>From Teradata_Table_1
> Where Deal_no in (Select Deal_no from SQLTable)
> ")
> Now I know that wont work, but How can I pass 184 Deal Numbers from my
> SQL server to this query before it is sent to the Teradata server to be
> done? Do I have to keep re-doing an in statement each month?

To do it with OPENQUERY you would have to use dynamic SQL to build
the SQL statement, and also to execute the OPENQUERY thing, as
OPENQUERY does not take parameters of any kind of whatsoever.

But cannot you not use a linked server instead:

SELECT t.*
FROM TeraSrvr.db.catalog.Teradata_Table_1 t
WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this, I will chekc on the link bit below. Can you help me
with how to do this in Dynamic SQL. I can't find how to get a list of
my numbers into a Var so I can pass them on and use them in the
openquery statement.

Thanks again

Doug

Erland Sommarskog wrote:
> (douglascfast@.hotmail.com) writes:
> > So what I want is this
> > Select * from OpenQuery(TeraSrvr, "
> > Select Col1
> > , Col2
> > , Col3
> >>From Teradata_Table_1
> > Where Deal_no in (Select Deal_no from SQLTable)
> > ")
> > Now I know that wont work, but How can I pass 184 Deal Numbers from my
> > SQL server to this query before it is sent to the Teradata server to be
> > done? Do I have to keep re-doing an in statement each month?
> To do it with OPENQUERY you would have to use dynamic SQL to build
> the SQL statement, and also to execute the OPENQUERY thing, as
> OPENQUERY does not take parameters of any kind of whatsoever.
> But cannot you not use a linked server instead:
> SELECT t.*
> FROM TeraSrvr.db.catalog.Teradata_Table_1 t
> WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(douglascfast@.hotmail.com) writes:
> Thanks for this, I will chekc on the link bit below. Can you help me
> with how to do this in Dynamic SQL. I can't find how to get a list of
> my numbers into a Var so I can pass them on and use them in the
> openquery statement.

Alas, in SQL 2000 the only safe way is to run a loop over the table.
There are some shortcuts, but they unrely undefined behaviour, so I
advise against such use.

For dynamic SQL in general, I have a longer article on the topic on
my web site: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OPENQUERY and parameters

I am running OPENQUERY against Oracle database via a linked server.
How can I provide parameters into the Select statement?
ThanksWhen I have created openquery statements I create the appropriate SQL on the
fly (which is a pain when you get to single quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:e9ae05NPIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
>|||On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am running OPENQUERY against Oracle database via a linked server.
> How can I provide parameters into the Select statement?
> Thanks
What you basically need to do is treat your query like you are writing
a String that contains your query, then EXEC the string at the end.
Any embeded quotation marks need to be "doubled", then you concatenate
your parameters using string concat symbols ( the + sign in SQL
Server ), then EXEC it at the end and you're golden.
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY( PISERVER,
''SELECT TAG, TIME, VALUE
FROM piarchive.piavg
WHERE TAG = '' + @.tagname + ''
AND TIMESTEP = '' + @.timestep + ''
AND TIME >= '' + @.startdatetime + ''
AND TIME <= '' + @.enddatetime + '' '' ) Q
'
EXEC (@.SQLSTR)
-- Scott|||Thanks to you both I got it working:
DECLARE @.SQLSTR NVARCHAR(4000)
SET @.SQLSTR ='
SELECT * FROM OPENQUERY(PST,
''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
work_asgn_id,
descr, trunc(job_work_date) as job_work_date
from EWM.ASSIGNMENT_HISTORY a
inner join EWM.TERMINAL t
on a.work_terminal = t.terminal
where trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY'''')' +
' and act_offduty_date_time is not null
and act_onduty_date_time is not null
and to_char(a.ASSIGNMENT_HISTORY_SID) in
(select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY b
where a.work_asgn_id = b.WORK_ASGN_ID
and a.WORK_TERMINAL = b.WORK_TERMINAL
and trunc(job_work_date) = to_date( '' + @.ForDate + '',
''''MM/DD/YYYY''''))''' +
')'
EXEC (@.SQLSTR)
I run it fine in Data tab, but in Layout when I try to assign an expression
to a field selecting dataset it says:
'DailySummary' dataset has no fields.
What's wrong?
"Orne" <polysillycon@.yahoo.com> wrote in message
news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
>
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott|||Try to click the refresh fields button (one of the buttons to the right of
the ...)
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Thanks to you both I got it working:
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY(PST,
> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> work_asgn_id,
> descr, trunc(job_work_date) as job_work_date
> from EWM.ASSIGNMENT_HISTORY a
> inner join EWM.TERMINAL t
> on a.work_terminal = t.terminal
> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY'''')' +
> ' and act_offduty_date_time is not null
> and act_onduty_date_time is not null
> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
> b
> where a.work_asgn_id = b.WORK_ASGN_ID
> and a.WORK_TERMINAL = b.WORK_TERMINAL
> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> ''''MM/DD/YYYY''''))''' +
> ')'
> EXEC (@.SQLSTR)
> I run it fine in Data tab, but in Layout when I try to assign an
> expression to a field selecting dataset it says:
> 'DailySummary' dataset has no fields.
> What's wrong?
>
> "Orne" <polysillycon@.yahoo.com> wrote in message
> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>|||Worked!!
I am also having infamous:
Invalid data for type "numeric".
Is there any fix available for this error?
Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Try to click the refresh fields button (one of the buttons to the right of
> the ...)
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from EWM.ASSIGNMENT_HISTORY
>> b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>|||I have never seen this error.
You could try creating a stored procedure. In the stored procedure create a
temp table. Then do this:
insert #yourtemptable select * from openquery(pst, @.SQLSTR)
select * from #yourtemptable
return
Note that you would have to rework your string again.
Now, you know for sure what your output types are (based on how you created
the temp table) and you can thoroughly test outside of RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Worked!!
> I am also having infamous:
> Invalid data for type "numeric".
> Is there any fix available for this error?
> Thanks
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> Try to click the refresh fields button (one of the buttons to the right
>> of the ...)
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark Goldin" <mgoldin@.ufandd.com> wrote in message
>> news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Thanks to you both I got it working:
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY(PST,
>> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
>> work_asgn_id,
>> descr, trunc(job_work_date) as job_work_date
>> from EWM.ASSIGNMENT_HISTORY a
>> inner join EWM.TERMINAL t
>> on a.work_terminal = t.terminal
>> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY'''')' +
>> ' and act_offduty_date_time is not null
>> and act_onduty_date_time is not null
>> and to_char(a.ASSIGNMENT_HISTORY_SID) in
>> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
>> EWM.ASSIGNMENT_HISTORY b
>> where a.work_asgn_id = b.WORK_ASGN_ID
>> and a.WORK_TERMINAL = b.WORK_TERMINAL
>> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
>> ''''MM/DD/YYYY''''))''' +
>> ')'
>> EXEC (@.SQLSTR)
>> I run it fine in Data tab, but in Layout when I try to assign an
>> expression to a field selecting dataset it says:
>> 'DailySummary' dataset has no fields.
>> What's wrong?
>>
>> "Orne" <polysillycon@.yahoo.com> wrote in message
>> news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
>> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am running OPENQUERY against Oracle database via a linked server.
>> How can I provide parameters into the Select statement?
>> Thanks
>> What you basically need to do is treat your query like you are writing
>> a String that contains your query, then EXEC the string at the end.
>> Any embeded quotation marks need to be "doubled", then you concatenate
>> your parameters using string concat symbols ( the + sign in SQL
>> Server ), then EXEC it at the end and you're golden.
>>
>> DECLARE @.SQLSTR NVARCHAR(4000)
>> SET @.SQLSTR =>> '
>> SELECT * FROM OPENQUERY( PISERVER,
>> ''SELECT TAG, TIME, VALUE
>> FROM piarchive.piavg
>> WHERE TAG = '' + @.tagname + ''
>> AND TIMESTEP = '' + @.timestep + ''
>> AND TIME >= '' + @.startdatetime + ''
>> AND TIME <= '' + @.enddatetime + '' '' ) Q
>> '
>> EXEC (@.SQLSTR)
>> -- Scott
>>
>>
>|||On Dec 12, 2:12 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this error.
> You could try creating a stored procedure. In the stored procedure create a
> temp table. Then do this:
> insert #yourtemptable select * from openquery(pst, @.SQLSTR)
> select * from #yourtemptable
> return
> Note that you would have to rework your string again.
> Now, you know for sure what your output types are (based on how you created
> the temp table) and you can thoroughly test outside of RS.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> news:%233wMgCPPIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
> > Worked!!
> > I am also having infamous:
> > Invalid data for type "numeric".
> > Is there any fix available for this error?
> > Thanks
> > "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in message
> >news:ebrTNpOPIHA.6036@.TK2MSFTNGP03.phx.gbl...
> >> Try to click the refresh fields button (one of the buttons to the right
> >> of the ...)
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Mark Goldin" <mgol...@.ufandd.com> wrote in message
> >>news:%23ZUGWiOPIHA.5400@.TK2MSFTNGP04.phx.gbl...
> >> Thanks to you both I got it working:
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY(PST,
> >> ''select to_char(a.ASSIGNMENT_HISTORY_SID) as ASSIGNMENT_HISTORY_SID,
> >> work_asgn_id,
> >> descr, trunc(job_work_date) as job_work_date
> >> from EWM.ASSIGNMENT_HISTORY a
> >> inner join EWM.TERMINAL t
> >> on a.work_terminal = t.terminal
> >> where trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY'''')' +
> >> ' and act_offduty_date_time is not null
> >> and act_onduty_date_time is not null
> >> and to_char(a.ASSIGNMENT_HISTORY_SID) in
> >> (select min(to_char(ASSIGNMENT_HISTORY_SID)) from
> >> EWM.ASSIGNMENT_HISTORY b
> >> where a.work_asgn_id = b.WORK_ASGN_ID
> >> and a.WORK_TERMINAL = b.WORK_TERMINAL
> >> and trunc(job_work_date) = to_date( '' + @.ForDate + '',
> >> ''''MM/DD/YYYY''''))''' +
> >> ')'
> >> EXEC (@.SQLSTR)
> >> I run it fine in Data tab, but in Layout when I try to assign an
> >> expression to a field selecting dataset it says:
> >> 'DailySummary' dataset has no fields.
> >> What's wrong?
> >> "Orne" <polysilly...@.yahoo.com> wrote in message
> >>news:938c7463-0737-4951-aa22-1f77d537383d@.i29g2000prf.googlegroups.com...
> >> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am running OPENQUERY against Oracle database via a linked server.
> >> How can I provide parameters into the Select statement?
> >> Thanks
> >> What you basically need to do is treat your query like you are writing
> >> a String that contains your query, then EXEC the string at the end.
> >> Any embeded quotation marks need to be "doubled", then you concatenate
> >> your parameters using string concat symbols ( the + sign in SQL
> >> Server ), then EXEC it at the end and you're golden.
> >> DECLARE @.SQLSTR NVARCHAR(4000)
> >> SET @.SQLSTR => >> '
> >> SELECT * FROM OPENQUERY( PISERVER,
> >> ''SELECT TAG, TIME, VALUE
> >> FROM piarchive.piavg
> >> WHERE TAG = '' + @.tagname + ''
> >> AND TIMESTEP = '' + @.timestep + ''
> >> AND TIME >= '' + @.startdatetime + ''
> >> AND TIME <= '' + @.enddatetime + '' '' ) Q
> >> '
> >> EXEC (@.SQLSTR)
> >> -- Scott- Hide quoted text -
> - Show quoted text -
Sounds like your work is like mine... SQL Server managing linked
servers to Oracle servers of all flavors...
Your @.ForDate parameter is a DateTime, but when it is passed as a
parameter, the formatting matters. My guess is that you are running
into errors with the parsing of either the TO_CHAR function or the
TO_DATE function.
Go to the Dataset Properties button [...], and goto the Parameters
tab. Change the Expression for ForDate from:
=Parameters!ForDate.Value
to
=Format( CDate( Parameters!ForDate.Value ), "MM/dd/yyyy" )
This will take your DateTime parameter and convert it into a String in
MM/DD/YYYY format, then the string will be concatenated to the rest of
the string and executed in SQL Server, which will pass through the SQL
query to Oracle and the TO_DATE function will always parse correctly.
The next item is that TO_CHAR( ASSIGNMENT_HISTORY_SID ) thing that you
got going everywhere. I would make sure that the
ASSIGNMENT_HISTORY_FIELD always contains a convertable number...
-- Scott|||On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > I am running OPENQUERY against Oracle database via a linked server.
> > How can I provide parameters into the Select statement?
> > Thanks
> What you basically need to do is treat your query like you are writing
> a String that contains your query, then EXEC the string at the end.
> Any embeded quotation marks need to be "doubled", then you concatenate
> your parameters using string concat symbols ( the + sign in SQL
> Server ), then EXEC it at the end and you're golden.
> DECLARE @.SQLSTR NVARCHAR(4000)
> SET @.SQLSTR => '
> SELECT * FROM OPENQUERY( PISERVER,
> ''SELECT TAG, TIME, VALUE
> FROM piarchive.piavg
> WHERE TAG = '' + @.tagname + ''
> AND TIMESTEP = '' + @.timestep + ''
> AND TIME >= '' + @.startdatetime + ''
> AND TIME <= '' + @.enddatetime + '' '' ) Q
> '
> EXEC (@.SQLSTR)
> -- Scott
Hi Orhne,
I was trying to do the same thing what you were trying, but I was not
able to syccessfully insert a parameter into my SQL query for Oracle
database.
--
(DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
--
This line should be parameterized. 11/01/2007 should be start date and
11/07/2007 is supposed to be the end date.
Please let me know on how to solve this issue, I am going to
incorporate this in SQL Reporting for generating reports. Please let
me know ASAP.
Thanks a lot.|||On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
> On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
>
>
> > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > I am running OPENQUERY against Oracle database via a linked server.
> > > How can I provide parameters into the Select statement?
> > > Thanks
> > What you basically need to do is treat your query like you are writing
> > a String that contains your query, then EXEC the string at the end.
> > Any embeded quotation marks need to be "doubled", then you concatenate
> > your parameters using string concat symbols ( the + sign in SQL
> > Server ), then EXEC it at the end and you're golden.
> > DECLARE @.SQLSTR NVARCHAR(4000)
> > SET @.SQLSTR => > '
> > SELECT * FROM OPENQUERY( PISERVER,
> > ''SELECT TAG, TIME, VALUE
> > FROM piarchive.piavg
> > WHERE TAG = '' + @.tagname + ''
> > AND TIMESTEP = '' + @.timestep + ''
> > AND TIME >= '' + @.startdatetime + ''
> > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > '
> > EXEC (@.SQLSTR)
> > -- Scott
> Hi Orhne,
> I was trying to do the same thing what you were trying, but I was not
> able to syccessfully insert a parameter into my SQL query for Oracle
> database.
> --
> (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> --
> This line should be parameterized. 11/01/2007 should be start date and
> 11/07/2007 is supposed to be the end date.
> Please let me know on how to solve this issue, I am going to
> incorporate this in SQL Reporting for generating reports. Please let
> me know ASAP.
> Thanks a lot.- Hide quoted text -
> - Show quoted text -
Try this, with quadruple single quotes. This first level is the '
that build the string, anything in that has to be doubled. You then
have another ' for the OPENQUERY function, so every quote in that has
to be doubled again:
SET @.SQLSTR = '
SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
SELECT * FROM DSS_CLIN.V_CLAIM_PAID
WHERE V_CLAIM_PAID.BATCH_DATE
BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
'' ) '
-- Scott|||I haven't had to do this for awhile because the code is stable but I wrote a
bunch of stored procedures to maintain a datamart. I was extracting data
from Sybase using linked servers (and unfortunately in SQL 2000 you had to
use openquery because four part naming was so awful). Anyway, lots and lots
of counting of single quotes.
One other point if on SQL 2005. If doing normal SQL statements, i.e. not any
Oracle extensions, then four part naming might work.
SELECT * FROM linkedservername.database.owner.tablename WHERE
V_CLAIM_PAID.BATCH_DATE
BETWEEN @.StartDate AND @.EndDate
In SQL 2000 this statement might have pulled all the records over. In SQL
2005 it realizes everything resides on the remote server and sends the whole
query over. You can use the queryplan statement to see if this is true
before running. Joins will work etc doing this. Always check query plan
first though.
I suggest in your work checking it out, there was such a dramatic difference
between versions with how well the four part naming worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Orne" <polysillycon@.yahoo.com> wrote in message
news:f34d6b78-9e05-4bc4-ba3e-05e2e8dc06db@.d21g2000prf.googlegroups.com...
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
Snip
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott|||On Dec 13, 8:50 am, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 12, 7:13 pm, tharani.mahend...@.gmail.com wrote:
>
>
> > On Dec 12, 10:12 am, Orne <polysilly...@.yahoo.com> wrote:
> > > On Dec 12, 11:45 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> > > > I am running OPENQUERY against Oracle database via a linked server.
> > > > How can I provide parameters into the Select statement?
> > > > Thanks
> > > What you basically need to do is treat your query like you are writing
> > > a String that contains your query, then EXEC the string at the end.
> > > Any embeded quotation marks need to be "doubled", then you concatenate
> > > your parameters using string concat symbols ( the + sign in SQL
> > > Server ), then EXEC it at the end and you're golden.
> > > DECLARE @.SQLSTR NVARCHAR(4000)
> > > SET @.SQLSTR => > > '
> > > SELECT * FROM OPENQUERY( PISERVER,
> > > ''SELECT TAG, TIME, VALUE
> > > FROM piarchive.piavg
> > > WHERE TAG = '' + @.tagname + ''
> > > AND TIMESTEP = '' + @.timestep + ''
> > > AND TIME >= '' + @.startdatetime + ''
> > > AND TIME <= '' + @.enddatetime + '' '' ) Q
> > > '
> > > EXEC (@.SQLSTR)
> > > -- Scott
> > Hi Orhne,
> > I was trying to do the same thing what you were trying, but I was not
> > able to syccessfully insert a parameter into my SQL query for Oracle
> > database.
> > --
> > (DSS_CLIN.V_CLAIM_PAID.BATCH_DATE between to_date(''11/01/2007'',''mm/
> > dd/yyyy'') and to_date(''11/07/2007'',''mm/dd/yyyy''))
> > --
> > This line should be parameterized. 11/01/2007 should be start date and
> > 11/07/2007 is supposed to be the end date.
> > Please let me know on how to solve this issue, I am going to
> > incorporate this in SQL Reporting for generating reports. Please let
> > me know ASAP.
> > Thanks a lot.- Hide quoted text -
> > - Show quoted text -
> Try this, with quadruple single quotes. This first level is the '
> that build the string, anything in that has to be doubled. You then
> have another ' for the OPENQUERY function, so every quote in that has
> to be doubled again:
> SET @.SQLSTR = '
> SELECT * FROM OPENQUERY( LINKEDSERVERNAME, ''
> SELECT * FROM DSS_CLIN.V_CLAIM_PAID
> WHERE V_CLAIM_PAID.BATCH_DATE
> BETWEEN TO_DATE( '' + @.StartDate + '', ''''MM/DD/YYYY'''' )
> AND TO_DATE( '' + @.EndDate + '', ''''MM/DD/YYYY'''' )
> '' ) '
> -- Scott- Hide quoted text -
> - Show quoted text -
This is the error which I am getting "Must declare the scalar variable
"@.StartDate".

Opening access via ip

Hi
sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
Regards
Hi
"John" wrote:

> Hi
> sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
John

Opening access via ip

Hi
sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
Regards
Hi
"John" wrote:

> Hi
> sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
John

Opening access via ip

Hi
sbs 2003 with ISA 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
RegardsHi
"John" wrote:

> Hi
> sbs 2003 with ISA 2000. I need to access an external (to the sbs/isa domai
n)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
Johnsql

opening .DTS package

Hi,
I got a .dts package via email. But I am not able to open it in my sql server.
Could some one pls help me know, how to open .dts packeges in the designer.
Thanks
Cheriyan.Inside of EM, right click on the 'Data Transformation Services' folder on the left and click 'Open Package...' and browse for it.sql

OpenDataSource()

Hello, I am trying to connect to a JBase database via an Attunity Connect
ODBC machine DSN but I'm struggling with the syntax. The DSN is called
ClarityODBC and this is the syntax I'm using:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityOD
BC;').CLARITY.[PUBLIC].
1;TRANS]
I get the following error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "(null)".
Obviously, I am doing something wrong but I haven't used OpenDataSource
before and I am struggling to find samples for the above scenario ie using a
System DSN and I'm not even sure if I should be using SQLOLEDB
Any help is greatly appreciated.
Many thanks
MarkHi, Mark,
From your description, I understand that:
You encountered the error :
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
When you executed the distributed query:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityOD
BC;').CLARITY.[PUBLIC].
1;TRANS]
If I have misunderstood, please let me know.
The error should be caused by not specifying authentication information.
If you would like to use Windows authentication, please add "Integrated
Security=SSPI" to the init_string parameter, for example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000
;Integrated
Security=SSPI').Northwind.dbo.Categories
If you use SQL Authentication, please add "User
ID=<userid>;Password=<password>" to the init_string parameter. It is
recommended that your password uses a strong name. For example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000
;User
Id=sa;Password=Password!01').Northwind.dbo.Categories
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

OpenDataSource()

Hello, I am trying to connect to a JBase database via an Attunity Connect
ODBC machine DSN but I'm struggling with the syntax. The DSN is called
ClarityODBC and this is the syntax I'm using:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityODBC;').CLAR ITY.[PUBLIC].[TRANS]
I get the following error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error.
Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for
linked server "(null)".
Obviously, I am doing something wrong but I haven't used OpenDataSource
before and I am struggling to find samples for the above scenario ie using a
System DSN and I'm not even sure if I should be using SQLOLEDB
Any help is greatly appreciated.
Many thanks
Mark
Hi, Mark,
From your description, I understand that:
You encountered the error :
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Invalid authorization specification".
When you executed the distributed query:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=ClarityODBC;').CLAR ITY.[PUBLIC].[TRANS]
If I have misunderstood, please let me know.
The error should be caused by not specifying authentication information.
If you would like to use Windows authentication, please add "Integrated
Security=SSPI" to the init_string parameter, for example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000;Integrate d
Security=SSPI').Northwind.dbo.Categories
If you use SQL Authentication, please add "User
ID=<userid>;Password=<password>" to the init_string parameter. It is
recommended that your password uses a strong name. For example:
SELECT *
FROM OPENdatasource('SQLOLEDB','dsn=MySQL2000;User
Id=sa;Password=Password!01').Northwind.dbo.Categor ies
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

open ports

what ports do I need to open in my router so that I can access my SQL server
via IP addr?
Thanks,
Raul RegoRaul,
TCP port 1433 should do it if I'm not mistaken, unless you changed the
default port.
--
HTH
Karl Gram
"Raul" <jjkgr@.hotmail.com> wrote in message
news:2Goje.13569$KQ6.13453@.trndny02...
> what ports do I need to open in my router so that I can access my SQL
> server via IP addr?
> Thanks,
> Raul Rego
>

open LDAP via MSSQLServer

Hi!
How can i access an open ldap server via MSSQLServer2000)?
Is there an oledb provider to connect to open ldap via linked server (for example)?
Thanks!
greets
chrisChris-

I believe the answer is NO. I did some research a while ago about the same. Just in case someone tells you otherwise, I will be interested too...

- CB

Originally posted by come_get_some
Hi!

How can i access an open ldap server via MSSQLServer2000)?

Is there an oledb provider to connect to open ldap via linked server (for example)?

Thanks!

greets

chris|||Hi!

Thanks for your answer.

I have to get open ldap data into SQLServer.......
I need to think about how to do it...

greetz

chris

Open and close SQL server via command prompt

I need command line to open and close SQL server
I'am working with a mirroring batch file, It performs a mirroring of a works
tation (database files) in a lab and a secured backuped server
Someone can help me
thanksI assume you mean start and stop the SQL Server service?
NET START MSSQLSERVER
NET STOP MSSQLSERVER
Don't forget to handle the SQL Server agent server, which is dependent on th
e SQL Server service. Also, if it
is a named instance, you need to adjust the service name (see the "Services"
windows applet).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Capone Raphael" <capone.raphael@.skynet.be> wrote in message
news:5409F787-DF3E-4B4F-A6FD-950B5AFA7268@.microsoft.com...
> I need command line to open and close SQL server
> I'am working with a mirroring batch file, It performs a mirroring of a workstation
(database files) in a lab
and a secured backuped server
> Someone can help me
> thanks|||I am not sure what you mean by "open and close." =20
If you need to connect via the command line, try osql.exe
--=20
Keith
"Capone Raphael" <capone.raphael@.skynet.be> wrote in message =
news:5409F787-DF3E-4B4F-A6FD-950B5AFA7268@.microsoft.com...
> I need command line to open and close SQL server=20
> I'am working with a mirroring batch file, It performs a mirroring of a =
workstation (database files) in a lab and a secured backuped server
>=20
> Someone can help me=20
>=20
> thanks

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.