Patch SP4 hanging

SP4 installation hangs. I used windows login to do the patch, a message
saying that it is not a valid user. I selected "retry" then used "sa". It
started hanging when "...MDAC" message disappeared.
I checked c:\winnt\sqlsp.log file. It is different from the files on the
other normal patched servers.
The last 2 lines in the file are:
End: Installpkgs(ID_INSTALLMDAC)
**** Starting open OP in write mode ****
What is the cause? How to fix it?Hi Julia
You may want to try using the MDAC component checker to see if you have a
consistent installation of MDAC and possibly loading MDAC MDAC 2.8 SP1 if on
Windows 2000 or MDAC 2.8 SP2 if on XP/Windows 2003 before installing the
service pack.
http://www.microsoft.com/downloads/results.aspx?productID=&freetext=mdac&DisplayLang=en
John
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:686DFD79-5223-4A80-BF1B-99469046BF5D@.microsoft.com...
> SP4 installation hangs. I used windows login to do the patch, a message
> saying that it is not a valid user. I selected "retry" then used "sa".
> It
> started hanging when "...MDAC" message disappeared.
> I checked c:\winnt\sqlsp.log file. It is different from the files on the
> other normal patched servers.
> The last 2 lines in the file are:
> End: Installpkgs(ID_INSTALLMDAC)
> **** Starting open OP in write mode ****
> What is the cause? How to fix it?

Password validation failed

Hello Group,
we got the following message when running execute sp_adduser
Server: Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy
requirements because it is not complex enough.
Server: Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15
is there a way to turn this off on sql server(2005sp2) level.
Thanks in advancedTal shalom
Firstv of all in SQL Server 2005 using CREATE USER ... is recommended way
instead of sp_adduser.
Now regarding to the error you are getting, it is because you created LOGIN
with 'weak' password.
Please run in query builder (take a look at CHECK_POLICY=OFF parameter in
the BOL)
create login tal with password ='t',
go
create user Joe for login tal
drop user joe
drop login tal
"Tal Bar-Or" <tal_baror@.hotmail.com> wrote in message
news:%23sZnW6gkHHA.3452@.TK2MSFTNGP04.phx.gbl...
> Hello Group,
> we got the following message when running execute sp_adduser
> Server: Msg 15118, Level 16, State 1, Line 1
> Password validation failed. The password does not meet Windows policy
> requirements because it is not complex enough.
> Server: Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15
> is there a way to turn this off on sql server(2005sp2) level.
> Thanks in advanced
>
>
>
>|||Shalom Uri,
Thanks for the answer. I understand that CHECK_POLICY=OFF can be done per
individual login . However can it be done on the SERVER level for ALL
logins. This would help us not nodify the
code when migrationg from SQL 2000 to SQL 2005
Thanks in advanced
Tal,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eApc3BhkHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Tal shalom
> Firstv of all in SQL Server 2005 using CREATE USER ... is recommended
> way instead of sp_adduser.
> Now regarding to the error you are getting, it is because you created
> LOGIN with 'weak' password.
> Please run in query builder (take a look at CHECK_POLICY=OFF parameter in
> the BOL)
> create login tal with password ='t',
> go
> create user Joe for login tal
> drop user joe
> drop login tal
>
>
> "Tal Bar-Or" <tal_baror@.hotmail.com> wrote in message
> news:%23sZnW6gkHHA.3452@.TK2MSFTNGP04.phx.gbl...
>|||Hi Tal
By default CHECK_POLICY=ON.CHECK_POLICY are only enforced on Windows Server
2003 so I have no idea how to change it .
"Tal Bar-Or" <tal_baror@.hotmail.com> wrote in message
news:%23n$ddThkHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Shalom Uri,
> Thanks for the answer. I understand that CHECK_POLICY=OFF can be done per
> individual login . However can it be done on the SERVER level for ALL
> logins. This would help us not nodify the
> code when migrationg from SQL 2000 to SQL 2005
> Thanks in advanced
> Tal,
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eApc3BhkHHA.2272@.TK2MSFTNGP02.phx.gbl...
>

Password protected stored procedure question

I have a password protected stored procedure that I have created.
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser."Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>>I have a password protected stored procedure that I have created.
>> If the user issues just the name of the stored procedure he gets a
>> message telling him that he needs to input the password.
>> If the user inputs the wrong password he gets a "syntax error" message
>> back in the query browser.
>> Now, here is the question:
>> How can I set it up so that the password shows up as asterisk in the
>> query browser?
>> Example - Sp_test password
>> Shows up as Sp_test ******** in the query browser.
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

password policy

I tried to unchecked the password policy for a user. But I got an error
message when I press the OK button, "CHeck policy and check expiration
cannot be off when must change is on". Can anyone please tell me where I can
turnoff the must_change option so that I can uncheck the password policy.
THanks.I am using SQL 2005 server.
" 00EricClapton" <EE@.yahoo.com> wrote in message
news:e4RjS01aHHA.4396@.TK2MSFTNGP06.phx.gbl...
>I tried to unchecked the password policy for a user. But I got an error
>message when I press the OK button, "CHeck policy and check expiration
>cannot be off when must change is on". Can anyone please tell me where I
>can turnoff the must_change option so that I can uncheck the password
>policy. THanks.
>|||Hello,
Best option is login using that user and change the password once it prompts
for a password and then disconnect. After that connect as SA and uncheck the
password policy.
This will work.
Thanks
Hari
" 00EricClapton" <EE@.yahoo.com> wrote in message
news:OfOPL41aHHA.4220@.TK2MSFTNGP03.phx.gbl...
>I am using SQL 2005 server.
>
> " 00EricClapton" <EE@.yahoo.com> wrote in message
> news:e4RjS01aHHA.4396@.TK2MSFTNGP06.phx.gbl...
>>I tried to unchecked the password policy for a user. But I got an error
>>message when I press the OK button, "CHeck policy and check expiration
>>cannot be off when must change is on". Can anyone please tell me where I
>>can turnoff the must_change option so that I can uncheck the password
>>policy. THanks.
>

Password Error... while installing SQL SP 3a

When I try to install SQL Server Service Pack 3a, I am getting the following
error message.
"Setup was unable to validate your password. Press retry to enter password
or press cancel to exit out of setup" "Retry and Cancel" buttons.
I am entering a valid password. What are the reasons that it may throw this
error message?
Also I noticed that My SQL server and SERVER agent are getting stopped while
doing this SP installation.
SQL 2K.
Any comments and thoughts. Thanks you,
Smith
I had a similiar occurance with SP4. My situatiuon was that the service
account for the engine was authenticating to another domain and I was logged
into the domain the machine was in via remote desktop. Once I altered the
service to use the account in the same domain as the machine, all was fine.
needless to say, some of our installs are jacked up.
"Branden Smith" wrote:

> When I try to install SQL Server Service Pack 3a, I am getting the following
> error message.
> "Setup was unable to validate your password. Press retry to enter password
> or press cancel to exit out of setup" "Retry and Cancel" buttons.
> I am entering a valid password. What are the reasons that it may throw this
> error message?
> Also I noticed that My SQL server and SERVER agent are getting stopped while
> doing this SP installation.
> SQL 2K.
> Any comments and thoughts. Thanks you,
> Smith
>
>
>
|||Both the services are running under "LocalSystem" account. After changing to
LocalSystem account I did restart both services. Something is missing here?
Do I have to reboot the computer?
Thanks for your reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> I had a similiar occurance with SP4. My situatiuon was that the service
> account for the engine was authenticating to another domain and I was
logged
> into the domain the machine was in via remote desktop. Once I altered the
> service to use the account in the same domain as the machine, all was
fine.[vbcol=seagreen]
> needless to say, some of our installs are jacked up.
> "Branden Smith" wrote:
following[vbcol=seagreen]
password[vbcol=seagreen]
this[vbcol=seagreen]
while[vbcol=seagreen]
|||Can you login to the server with the credentials you are supplying in QA or EM?
"Branden Smith" wrote:

> Both the services are running under "LocalSystem" account. After changing to
> LocalSystem account I did restart both services. Something is missing here?
> Do I have to reboot the computer?
> Thanks for your reply.
> Smith
>
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> logged
> fine.
> following
> password
> this
> while
>
>
|||Yes. It worked well with EM. I did not test with QA.
I do not know why it is failing for password. Any other thoughts. Thanks for
the reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D73EDBE1-02EA-4795-A550-E64883E383D9@.microsoft.com...
> Can you login to the server with the credentials you are supplying in QA
or EM?[vbcol=seagreen]
> "Branden Smith" wrote:
changing to[vbcol=seagreen]
here?[vbcol=seagreen]
in[vbcol=seagreen]
service[vbcol=seagreen]
the[vbcol=seagreen]
throw[vbcol=seagreen]
stopped[vbcol=seagreen]

Password Error... while installing SQL SP 3a

When I try to install SQL Server Service Pack 3a, I am getting the following
error message.
"Setup was unable to validate your password. Press retry to enter password
or press cancel to exit out of setup" "Retry and Cancel" buttons.
I am entering a valid password. What are the reasons that it may throw this
error message?
Also I noticed that My SQL server and SERVER agent are getting stopped while
doing this SP installation.
SQL 2K.
Any comments and thoughts. Thanks you,
SmithI had a similiar occurance with SP4. My situatiuon was that the service
account for the engine was authenticating to another domain and I was logged
into the domain the machine was in via remote desktop. Once I altered the
service to use the account in the same domain as the machine, all was fine.
needless to say, some of our installs are jacked up.
"Branden Smith" wrote:
> When I try to install SQL Server Service Pack 3a, I am getting the following
> error message.
> "Setup was unable to validate your password. Press retry to enter password
> or press cancel to exit out of setup" "Retry and Cancel" buttons.
> I am entering a valid password. What are the reasons that it may throw this
> error message?
> Also I noticed that My SQL server and SERVER agent are getting stopped while
> doing this SP installation.
> SQL 2K.
> Any comments and thoughts. Thanks you,
> Smith
>
>
>|||Both the services are running under "LocalSystem" account. After changing to
LocalSystem account I did restart both services. Something is missing here?
Do I have to reboot the computer?
Thanks for your reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> I had a similiar occurance with SP4. My situatiuon was that the service
> account for the engine was authenticating to another domain and I was
logged
> into the domain the machine was in via remote desktop. Once I altered the
> service to use the account in the same domain as the machine, all was
fine.
> needless to say, some of our installs are jacked up.
> "Branden Smith" wrote:
> > When I try to install SQL Server Service Pack 3a, I am getting the
following
> > error message.
> >
> > "Setup was unable to validate your password. Press retry to enter
password
> > or press cancel to exit out of setup" "Retry and Cancel" buttons.
> >
> > I am entering a valid password. What are the reasons that it may throw
this
> > error message?
> >
> > Also I noticed that My SQL server and SERVER agent are getting stopped
while
> > doing this SP installation.
> >
> > SQL 2K.
> >
> > Any comments and thoughts. Thanks you,
> >
> > Smith
> >
> >
> >
> >
> >|||Can you login to the server with the credentials you are supplying in QA or EM?
"Branden Smith" wrote:
> Both the services are running under "LocalSystem" account. After changing to
> LocalSystem account I did restart both services. Something is missing here?
> Do I have to reboot the computer?
> Thanks for your reply.
> Smith
>
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> > I had a similiar occurance with SP4. My situatiuon was that the service
> > account for the engine was authenticating to another domain and I was
> logged
> > into the domain the machine was in via remote desktop. Once I altered the
> > service to use the account in the same domain as the machine, all was
> fine.
> > needless to say, some of our installs are jacked up.
> >
> > "Branden Smith" wrote:
> >
> > > When I try to install SQL Server Service Pack 3a, I am getting the
> following
> > > error message.
> > >
> > > "Setup was unable to validate your password. Press retry to enter
> password
> > > or press cancel to exit out of setup" "Retry and Cancel" buttons.
> > >
> > > I am entering a valid password. What are the reasons that it may throw
> this
> > > error message?
> > >
> > > Also I noticed that My SQL server and SERVER agent are getting stopped
> while
> > > doing this SP installation.
> > >
> > > SQL 2K.
> > >
> > > Any comments and thoughts. Thanks you,
> > >
> > > Smith
> > >
> > >
> > >
> > >
> > >
>
>|||Yes. It worked well with EM. I did not test with QA.
I do not know why it is failing for password. Any other thoughts. Thanks for
the reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D73EDBE1-02EA-4795-A550-E64883E383D9@.microsoft.com...
> Can you login to the server with the credentials you are supplying in QA
or EM?
> "Branden Smith" wrote:
> > Both the services are running under "LocalSystem" account. After
changing to
> > LocalSystem account I did restart both services. Something is missing
here?
> > Do I have to reboot the computer?
> > Thanks for your reply.
> > Smith
> >
> >
> >
> >
> > "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
in
> > message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> > > I had a similiar occurance with SP4. My situatiuon was that the
service
> > > account for the engine was authenticating to another domain and I was
> > logged
> > > into the domain the machine was in via remote desktop. Once I altered
the
> > > service to use the account in the same domain as the machine, all was
> > fine.
> > > needless to say, some of our installs are jacked up.
> > >
> > > "Branden Smith" wrote:
> > >
> > > > When I try to install SQL Server Service Pack 3a, I am getting the
> > following
> > > > error message.
> > > >
> > > > "Setup was unable to validate your password. Press retry to enter
> > password
> > > > or press cancel to exit out of setup" "Retry and Cancel" buttons.
> > > >
> > > > I am entering a valid password. What are the reasons that it may
throw
> > this
> > > > error message?
> > > >
> > > > Also I noticed that My SQL server and SERVER agent are getting
stopped
> > while
> > > > doing this SP installation.
> > > >
> > > > SQL 2K.
> > > >
> > > > Any comments and thoughts. Thanks you,
> > > >
> > > > Smith
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Password Error... while installing SQL SP 3a

When I try to install SQL Server Service Pack 3a, I am getting the following
error message.
"Setup was unable to validate your password. Press retry to enter password
or press cancel to exit out of setup" "Retry and Cancel" buttons.
I am entering a valid password. What are the reasons that it may throw this
error message?
Also I noticed that My SQL server and SERVER agent are getting stopped while
doing this SP installation.
SQL 2K.
Any comments and thoughts. Thanks you,
SmithI had a similiar occurance with SP4. My situatiuon was that the service
account for the engine was authenticating to another domain and I was logged
into the domain the machine was in via remote desktop. Once I altered the
service to use the account in the same domain as the machine, all was fine.
needless to say, some of our installs are jacked up.
"Branden Smith" wrote:

> When I try to install SQL Server Service Pack 3a, I am getting the followi
ng
> error message.
> "Setup was unable to validate your password. Press retry to enter password
> or press cancel to exit out of setup" "Retry and Cancel" buttons.
> I am entering a valid password. What are the reasons that it may throw thi
s
> error message?
> Also I noticed that My SQL server and SERVER agent are getting stopped whi
le
> doing this SP installation.
> SQL 2K.
> Any comments and thoughts. Thanks you,
> Smith
>
>
>|||Both the services are running under "LocalSystem" account. After changing to
LocalSystem account I did restart both services. Something is missing here?
Do I have to reboot the computer?
Thanks for your reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> I had a similiar occurance with SP4. My situatiuon was that the service
> account for the engine was authenticating to another domain and I was
logged
> into the domain the machine was in via remote desktop. Once I altered the
> service to use the account in the same domain as the machine, all was
fine.[vbcol=seagreen]
> needless to say, some of our installs are jacked up.
> "Branden Smith" wrote:
>
following[vbcol=seagreen]
password[vbcol=seagreen]
this[vbcol=seagreen]
while[vbcol=seagreen]|||Can you login to the server with the credentials you are supplying in QA or
EM?
"Branden Smith" wrote:

> Both the services are running under "LocalSystem" account. After changing
to
> LocalSystem account I did restart both services. Something is missing here
?
> Do I have to reboot the computer?
> Thanks for your reply.
> Smith
>
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:3DF72A14-2DC6-40A4-9A54-04FFE4869D48@.microsoft.com...
> logged
> fine.
> following
> password
> this
> while
>
>|||Yes. It worked well with EM. I did not test with QA.
I do not know why it is failing for password. Any other thoughts. Thanks for
the reply.
Smith
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D73EDBE1-02EA-4795-A550-E64883E383D9@.microsoft.com...
> Can you login to the server with the credentials you are supplying in QA
or EM?[vbcol=seagreen]
> "Branden Smith" wrote:
>
changing to[vbcol=seagreen]
here?[vbcol=seagreen]
in[vbcol=seagreen]
service[vbcol=seagreen]
the[vbcol=seagreen]
throw[vbcol=seagreen]
stopped[vbcol=seagreen]

passing report parameters to an Oracle proc

Hi,
I received an error message after running a report by pushing the Preview
button:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Here is the Oracle proc:
CREATE OR REPLACE PACKAGE BODY MPC IS
PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur
OUT T_RESULT_CURSOR)
Here is the Query string:
"MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
At the same time, in the Parameters tab of the Dataset screen, I also create
two parameters:
Name= :ip_response_interval
Value= =Parameters!ip_response_interval.Value
Name= :ip_launch_list
Vaue= =Parameters!ip_launch_list.Value
However, if I click the generic query design button and just type
"MPC.Report_Performance_Main_5" and click the Run button, it pops out the
"Define Query Parameters" screen. After typing the necessary data and hit the
"OK" button, I am able to see the query result in the buttom pane.
I am confused what's wrong when I hit the Preview button.
Any clues will be very appreciated!
JamesNot sure but I think in the generic query window you can do this:
MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
It should create the report parameters for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> Hi,
> I received an error message after running a report by pushing the Preview
> button:
> ORA-00972:identifier is too long
> ORA-06512:at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Here is the Oracle proc:
> CREATE OR REPLACE PACKAGE BODY MPC IS
> PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> ip_launch_list IN VARCHAR2,
> results_cur
> OUT T_RESULT_CURSOR)
> Here is the Query string:
> "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> At the same time, in the Parameters tab of the Dataset screen, I also
> create
> two parameters:
> Name= :ip_response_interval
> Value= =Parameters!ip_response_interval.Value
> Name= :ip_launch_list
> Vaue= =Parameters!ip_launch_list.Value
> However, if I click the generic query design button and just type
> "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> "Define Query Parameters" screen. After typing the necessary data and hit
> the
> "OK" button, I am able to see the query result in the buttom pane.
> I am confused what's wrong when I hit the Preview button.
> Any clues will be very appreciated!
> James
>|||I did this in the generic query and got the following error:
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Is there any document about call a Oracle proc?
Thanks,
James
"Bruce L-C [MVP]" wrote:
> Not sure but I think in the generic query window you can do this:
> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> It should create the report parameters for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > Hi,
> >
> > I received an error message after running a report by pushing the Preview
> > button:
> >
> > ORA-00972:identifier is too long
> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Here is the Oracle proc:
> >
> > CREATE OR REPLACE PACKAGE BODY MPC IS
> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> >
> > ip_launch_list IN VARCHAR2,
> > results_cur
> > OUT T_RESULT_CURSOR)
> >
> > Here is the Query string:
> > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> >
> > At the same time, in the Parameters tab of the Dataset screen, I also
> > create
> > two parameters:
> > Name= :ip_response_interval
> > Value= =Parameters!ip_response_interval.Value
> >
> > Name= :ip_launch_list
> > Vaue= =Parameters!ip_launch_list.Value
> >
> > However, if I click the generic query design button and just type
> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > "Define Query Parameters" screen. After typing the necessary data and hit
> > the
> > "OK" button, I am able to see the query result in the buttom pane.
> >
> > I am confused what's wrong when I hit the Preview button.
> >
> > Any clues will be very appreciated!
> >
> > James
> >
> >
>
>|||Here is some misc things I've kept around. I haven't used Oracle with RS
(although I did use 8.1.7 extensively in the past).
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:14B75C3F-7959-47B9-A563-EF5436510BFC@.microsoft.com...
>I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
>> Not sure but I think in the generic query window you can do this:
>> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
>> It should create the report parameters for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
>> > Hi,
>> >
>> > I received an error message after running a report by pushing the
>> > Preview
>> > button:
>> >
>> > ORA-00972:identifier is too long
>> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
>> > ORA-06512: at line 1
>> >
>> > Here is the Oracle proc:
>> >
>> > CREATE OR REPLACE PACKAGE BODY MPC IS
>> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
>> >
>> > ip_launch_list IN VARCHAR2,
>> >
>> > results_cur
>> > OUT T_RESULT_CURSOR)
>> >
>> > Here is the Query string:
>> > "MPC"."Report_Performance_Main_5 :ip_response_interval,
>> > :ip_launch_list"
>> >
>> > At the same time, in the Parameters tab of the Dataset screen, I also
>> > create
>> > two parameters:
>> > Name= :ip_response_interval
>> > Value= =Parameters!ip_response_interval.Value
>> >
>> > Name= :ip_launch_list
>> > Vaue= =Parameters!ip_launch_list.Value
>> >
>> > However, if I click the generic query design button and just type
>> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out
>> > the
>> > "Define Query Parameters" screen. After typing the necessary data and
>> > hit
>> > the
>> > "OK" button, I am able to see the query result in the buttom pane.
>> >
>> > I am confused what's wrong when I hit the Preview button.
>> >
>> > Any clues will be very appreciated!
>> >
>> > James
>> >
>> >
>>|||James:
Any luck making this successful..I am strugling with this issue as well.
Please reply if you have this working,
"James" wrote:
> I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
> > Not sure but I think in the generic query window you can do this:
> >
> > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> >
> > It should create the report parameters for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "James" <James@.discussions.microsoft.com> wrote in message
> > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > Hi,
> > >
> > > I received an error message after running a report by pushing the Preview
> > > button:
> > >
> > > ORA-00972:identifier is too long
> > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Here is the Oracle proc:
> > >
> > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > >
> > > ip_launch_list IN VARCHAR2,
> > > results_cur
> > > OUT T_RESULT_CURSOR)
> > >
> > > Here is the Query string:
> > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > >
> > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > create
> > > two parameters:
> > > Name= :ip_response_interval
> > > Value= =Parameters!ip_response_interval.Value
> > >
> > > Name= :ip_launch_list
> > > Vaue= =Parameters!ip_launch_list.Value
> > >
> > > However, if I click the generic query design button and just type
> > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > the
> > > "OK" button, I am able to see the query result in the buttom pane.
> > >
> > > I am confused what's wrong when I hit the Preview button.
> > >
> > > Any clues will be very appreciated!
> > >
> > > James
> > >
> > >
> >
> >
> >|||anyone lucky enough to make oracle refcursor working under RS? kindly share
with us, thanks...
"MSOracle" wrote:
> James:
> Any luck making this successful..I am strugling with this issue as well.
> Please reply if you have this working,
> "James" wrote:
> > I did this in the generic query and got the following error:
> > An error occurred while retrieving the parameters in the query.
> > ORA-00911: invalid character
> > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Is there any document about call a Oracle proc?
> >
> > Thanks,
> >
> > James
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Not sure but I think in the generic query window you can do this:
> > >
> > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > >
> > > It should create the report parameters for you.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "James" <James@.discussions.microsoft.com> wrote in message
> > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > Hi,
> > > >
> > > > I received an error message after running a report by pushing the Preview
> > > > button:
> > > >
> > > > ORA-00972:identifier is too long
> > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > ORA-06512: at line 1
> > > >
> > > > Here is the Oracle proc:
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > >
> > > > ip_launch_list IN VARCHAR2,
> > > > results_cur
> > > > OUT T_RESULT_CURSOR)
> > > >
> > > > Here is the Query string:
> > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > >
> > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > create
> > > > two parameters:
> > > > Name= :ip_response_interval
> > > > Value= =Parameters!ip_response_interval.Value
> > > >
> > > > Name= :ip_launch_list
> > > > Vaue= =Parameters!ip_launch_list.Value
> > > >
> > > > However, if I click the generic query design button and just type
> > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > the
> > > > "OK" button, I am able to see the query result in the buttom pane.
> > > >
> > > > I am confused what's wrong when I hit the Preview button.
> > > >
> > > > Any clues will be very appreciated!
> > > >
> > > > James
> > > >
> > > >
> > >
> > >
> > >|||I have been able to do i just recenly but Only after installing the Oracle
10g client. & using the Orcale provider.
"J" wrote:
> anyone lucky enough to make oracle refcursor working under RS? kindly share
> with us, thanks...
> "MSOracle" wrote:
> > James:
> >
> > Any luck making this successful..I am strugling with this issue as well.
> > Please reply if you have this working,
> >
> > "James" wrote:
> >
> > > I did this in the generic query and got the following error:
> > > An error occurred while retrieving the parameters in the query.
> > > ORA-00911: invalid character
> > > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Is there any document about call a Oracle proc?
> > >
> > > Thanks,
> > >
> > > James
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Not sure but I think in the generic query window you can do this:
> > > >
> > > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > > >
> > > > It should create the report parameters for you.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "James" <James@.discussions.microsoft.com> wrote in message
> > > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I received an error message after running a report by pushing the Preview
> > > > > button:
> > > > >
> > > > > ORA-00972:identifier is too long
> > > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > > ORA-06512: at line 1
> > > > >
> > > > > Here is the Oracle proc:
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > > >
> > > > > ip_launch_list IN VARCHAR2,
> > > > > results_cur
> > > > > OUT T_RESULT_CURSOR)
> > > > >
> > > > > Here is the Query string:
> > > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > > >
> > > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > > create
> > > > > two parameters:
> > > > > Name= :ip_response_interval
> > > > > Value= =Parameters!ip_response_interval.Value
> > > > >
> > > > > Name= :ip_launch_list
> > > > > Vaue= =Parameters!ip_launch_list.Value
> > > > >
> > > > > However, if I click the generic query design button and just type
> > > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > > the
> > > > > "OK" button, I am able to see the query result in the buttom pane.
> > > > >
> > > > > I am confused what's wrong when I hit the Preview button.
> > > > >
> > > > > Any clues will be very appreciated!
> > > > >
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > > >