link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default...b;en-us;280106
http://support.microsoft.com/default...b;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error mess
age
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

Link to Oracle database

Hi all,
I have installed Oracle client tool on the SQL server and have checked the tns file. Everythings look fine, even from the server I can run SQL Plus and connect to the Oracle database. When I create a link and try to refresh tables under linked serve, I ge
t this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.8, but I get the same error.
Any help would be appreciated.
Shahri
That's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 16 Jun 2004 08:43:37 -0700, Shahri
<Shahri@.discussions.microsoft.com> wrote:

>Hi all,
>I have installed Oracle client tool on the SQL server and have checked the tns file. Everythings look fine, even from the server I can run SQL Plus and connect to the Oracle database. When I create a link and try to refresh tables under linked serve, I g
et this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.8, but I get the same error.
>Any help would be appreciated.
>Shahri

Link to Oracle database

Hi all,
I have installed Oracle client tool on the SQL server and have checked the t
ns file. Everythings look fine, even from the server I can run SQL Plus and
connect to the Oracle database. When I create a link and try to refresh tabl
es under linked serve, I ge
t this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I rein
stalled MDAC 2.8, but I get the same error.
Any help would be appreciated.
ShahriThat's a generic error. Try turning on trace flag 7300 or
us SQL Profiler to capture the OLEDB Errors event. This
should give you to get more information about the error.
Additionally, you'll want to check the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 16 Jun 2004 08:43:37 -0700, Shahri
<Shahri@.discussions.microsoft.com> wrote:

>Hi all,
>I have installed Oracle client tool on the SQL server and have checked the tns file
. Everythings look fine, even from the server I can run SQL Plus and connect to the
Oracle database. When I create a link and try to refresh tables under linked serve,
I g
et this error: Error 7399:OLE DB provider 'MSDAORA' reported an error. I reinstalled MDAC 2.
8, but I get the same error.
>Any help would be appreciated.
>Shahri

Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
Raj
With what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>
|||I was experiencing the exact same issue.
I solved it by using absolute paths in place of drive letters when defining the linked server
So in place of z:\folder\file.mdb I wrote \\servername\share\folder\file.mdb
this solved the issue for me

Quote:

Originally Posted by Raj

HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
RajWith what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
RajWith what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj
>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>sql

link from sql2005 (64-bit) to sql 7 (32-bit).

I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"Jos" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
I get the following error:
OLE DB provider "SQLNCLI" for linked server "sql7" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "sql7". The provider supports the interface, but
returns a failure code when it is used.
How can I get these servers linked?
Regards,|||I just ran into a similar problem, but from 64 bit SQL 2005 trying to link
to 32 bit SQL 2000. What I did on the 2000 server was run the instcat.sql
from the latest service pack installation I did on the 2000 server. In my
case it was SP3a. There are instructions to change the last insert statement
to the table to reflect the version of SQL Server (select @.@.version) that
you are running. In my case it was .977. That fixed my problems.
Jackie
"Jos" <josedc@.starmedia.com> wrote in message
news:OE2iT1GlGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Hi: I'm trying to link from sql2005 (64-bit) SP1 to sql 7 (32-bit) SP4.
> I get the following error:
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "sql7" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "sql7". The provider supports the interface,
> but
> returns a failure code when it is used.
> How can I get these servers linked?
> Regards,
>

Link Between SQL Server 2000 and MSDE Not working! Please HELP !

Hello. I have a SQL Server 2000 Database set up on our network.
We want to run a process on another machine. This machine has MSDE Installed
on it. I have created a new entry in the Linked Server options of the MSDE
Machine to our SQL Server.
However, on trying to query the SQL Server 2000 tables from the MSDE
Database, I get this error:
Error 7302: Could not create an instance of OLE DB Provider 'SQLOLEDB'
I have checked the MDAC_TYP using the MDAC_TYP Checker, and both the SQL
Server and MSDE Machines have the same version running.
I have pinged the SQL Server from the MSDE Server and they can both see each
other.
Any suggestions you might have would be greatly appreciated!!
Thanking you in advance,
m00nm0nkey!!
In message <29E143E6-25FB-4EFF-88B9-D9EE8E23122C@.microsoft.com>,
m00nm0nkey <m00nm0nkey@.discussions.microsoft.com> writes
>Hello. I have a SQL Server 2000 Database set up on our network.
>We want to run a process on another machine. This machine has MSDE Installed
>on it. I have created a new entry in the Linked Server options of the MSDE
>Machine to our SQL Server.
>However, on trying to query the SQL Server 2000 tables from the MSDE
>Database, I get this error:
>Error 7302: Could not create an instance of OLE DB Provider 'SQLOLEDB'
>I have checked the MDAC_TYP using the MDAC_TYP Checker, and both the SQL
>Server and MSDE Machines have the same version running.
>I have pinged the SQL Server from the MSDE Server and they can both see each
>other.
>Any suggestions you might have would be greatly appreciated!!
>Thanking you in advance,
>
Pinging the servers does not prove anything other than each PC is on the
network. Can you connect to the MSDE Server through Enterprise Manager
(adding it to the default Server Group) on the SQL Server machine.
If yes, it is most likely something wrong in how you have defined the
link or referencing the SQL Server in your queries.
If no, then it would suggest the MSDE Server does not have the Network
Protocols enabled (turned off by default in a normal installation).
Alternatively, you may have a firewall or packet filtering enabled on
the MSDE machine blocking ports 1433 (TCP) and 1434 (UDP).
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Lines in text column run together

Hi,
I'm using SQL RS 2000. My report has a text column that contains line
breaks. When printing this column in on a report, all of the lines run
together.
I've tried using a grid and list view and neither work.
Any ideas? Is there a function I should be using?
Thanks.
- JeffAre you using vbcrlf for the line breaks? I use that and it has always
worked for me.
"Jeff" wrote:
> Hi,
> I'm using SQL RS 2000. My report has a text column that contains line
> breaks. When printing this column in on a report, all of the lines run
> together.
> I've tried using a grid and list view and neither work.
> Any ideas? Is there a function I should be using?
> Thanks.
> - Jeff|||What do I do with the vrcrlf? The source application SQL RS is reading from
is a Great Plains text column. So I don't have control over how the data
enters the field. I just want to report it out.
- J
"SFrench" wrote:
> Are you using vbcrlf for the line breaks? I use that and it has always
> worked for me.
> "Jeff" wrote:
> > Hi,
> >
> > I'm using SQL RS 2000. My report has a text column that contains line
> > breaks. When printing this column in on a report, all of the lines run
> > together.
> >
> > I've tried using a grid and list view and neither work.
> >
> > Any ideas? Is there a function I should be using?
> >
> > Thanks.
> >
> > - Jeffsql

Limits on parameters and query complexity?

I have a huge SQL statement with subqueries in the From and Where clauses.
When I run it in SQL Query Analyzer, it runs fine, but if I put it into the
Data tab of a report, Visual Studio just hangs.
I've been able to create the parameter list manually (adding them to the
Report Parameters dialog), and then when I paste the query in without the
Where clause, it works ok. I can then start adding lines to the Where
clause, but as soon as I get enough lines for about 10 parameters or so, it
hangs up again.
I know nothing's wrong with the SQL itself since it runs in Query Analyzer
without a hitch; am I looking at a bug in RS?Sounds like a bug. If you have a good repro, please send it to me at
bradsy@.microsoft.com
--
| From: "DJM" <msnews@.puddlestheshark.com>
| Subject: Limits on parameters and query complexity?
| Date: Mon, 14 Feb 2005 16:53:09 -0700
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <uG8e6AvEFHA.548@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 64.140.73.34
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35923
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a huge SQL statement with subqueries in the From and Where
clauses.
| When I run it in SQL Query Analyzer, it runs fine, but if I put it into
the
| Data tab of a report, Visual Studio just hangs.
|
| I've been able to create the parameter list manually (adding them to the
| Report Parameters dialog), and then when I paste the query in without the
| Where clause, it works ok. I can then start adding lines to the Where
| clause, but as soon as I get enough lines for about 10 parameters or so,
it
| hangs up again.
|
| I know nothing's wrong with the SQL itself since it runs in Query
Analyzer
| without a hitch; am I looking at a bug in RS?
|
|
|

Limiting size of a table in SQL server

hi,

im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?Using a scheduled job in SQL Server would likely be the easiest. Create the code to do what you want in a stored procedure, and then schedule the stored procedure.

In Enterprise Manager, expand the database till you see Management, expand that and right click on Jobs below it and select New Job to create and schedule the job.

Limit to number of databases in Analysis Services 2005

We are trying to run a load test on analysis services 2005. We were
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> > We are trying to run a load test on analysis services 2005. We were
> > going to start with 30 different databases out there and see what the
> > processing loads were. Analysis Services is stopping us at 25
> > databases and telling us that there is no more room. We have 260 GB
> > free on the server. Has anyone run into this before? Is there a limit
> > to the number of databases you can have? Does it vary by SQL edition?
> >

Limit to number of databases in Analysis Services 2005

We are trying to run a load test on analysis services 2005. We were
going to start with 30 different databases out there and see what the
processing loads were. Analysis Services is stopping us at 25
databases and telling us that there is no more room. We have 260 GB
free on the server. Has anyone run into this before? Is there a limit
to the number of databases you can have? Does it vary by SQL edition?http://msdn2.microsoft.com/en-us/library/ms365363.aspx
<rbergstrom@.spectrumhr.com> wrote in message
news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...
> We are trying to run a load test on analysis services 2005. We were
> going to start with 30 different databases out there and see what the
> processing loads were. Analysis Services is stopping us at 25
> databases and telling us that there is no more room. We have 260 GB
> free on the server. Has anyone run into this before? Is there a limit
> to the number of databases you can have? Does it vary by SQL edition?
>|||Thanks for that link. Now I will ask a dumb question. in the page on
that link it shows Maximum sizes/numbers. The value for Databases in
an instance is 2^31-1 = 2,147,483,647. Is that a total size in bytes?
The number of databases? I don't understand what that is supposed to
represent. Thanks for your help.
Immy wrote:[vbcol=seagreen]
> http://msdn2.microsoft.com/en-us/library/ms365363.aspx
> <rbergstrom@.spectrumhr.com> wrote in message
> news:1154470182.648978.116860@.75g2000cwc.googlegroups.com...

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanks
SQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:
> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

Limit server resources per query

Hello,
I've got a huge query that takes a fair amount of time to
run, and ideally this query will be run in the middle of
the night, so I wont have any issues with any customer
facing applications...
However in testing, I need to develop this report in the
daytime, and dont have the liberty of having a development
server. I was curious if in a sql statement, I could
specify that I'd rather have a query take longer, than
prevent other applications from being able to process data
in a timely fashion. (I get timeouts etc in the other apps)
As it sits this query takes about 4 minutes on a quite
fast sql server, and I dont mind it so much, but it seems
in that 4 minutes, other services are really hurting.
Thanks in advance,
Weston Weems
There is no option such as the one you describe but you can add MAXDOP hints
to the sql statements that will limit the number of processors used by the
query. So if you have 4 procs you can set it to 2 and leave 2 for the other
users. It may take longer but should be more respectful of the other users.
Andrew J. Kelly SQL MVP
"Weston Weems" <anonymous@.discussions.microsoft.com> wrote in message
news:0ae401c51848$8b06fc90$a501280a@.phx.gbl...
> Hello,
> I've got a huge query that takes a fair amount of time to
> run, and ideally this query will be run in the middle of
> the night, so I wont have any issues with any customer
> facing applications...
> However in testing, I need to develop this report in the
> daytime, and dont have the liberty of having a development
> server. I was curious if in a sql statement, I could
> specify that I'd rather have a query take longer, than
> prevent other applications from being able to process data
> in a timely fashion. (I get timeouts etc in the other apps)
> As it sits this query takes about 4 minutes on a quite
> fast sql server, and I dont mind it so much, but it seems
> in that 4 minutes, other services are really hurting.
> Thanks in advance,
> Weston Weems

Limit server resources per query

Hello,
I've got a huge query that takes a fair amount of time to
run, and ideally this query will be run in the middle of
the night, so I wont have any issues with any customer
facing applications...
However in testing, I need to develop this report in the
daytime, and dont have the liberty of having a development
server. I was curious if in a sql statement, I could
specify that I'd rather have a query take longer, than
prevent other applications from being able to process data
in a timely fashion. (I get timeouts etc in the other apps)
As it sits this query takes about 4 minutes on a quite
fast sql server, and I dont mind it so much, but it seems
in that 4 minutes, other services are really hurting.
Thanks in advance,
Weston WeemsThere is no option such as the one you describe but you can add MAXDOP hints
to the sql statements that will limit the number of processors used by the
query. So if you have 4 procs you can set it to 2 and leave 2 for the other
users. It may take longer but should be more respectful of the other users.
--
Andrew J. Kelly SQL MVP
"Weston Weems" <anonymous@.discussions.microsoft.com> wrote in message
news:0ae401c51848$8b06fc90$a501280a@.phx.gbl...
> Hello,
> I've got a huge query that takes a fair amount of time to
> run, and ideally this query will be run in the middle of
> the night, so I wont have any issues with any customer
> facing applications...
> However in testing, I need to develop this report in the
> daytime, and dont have the liberty of having a development
> server. I was curious if in a sql statement, I could
> specify that I'd rather have a query take longer, than
> prevent other applications from being able to process data
> in a timely fashion. (I get timeouts etc in the other apps)
> As it sits this query takes about 4 minutes on a quite
> fast sql server, and I dont mind it so much, but it seems
> in that 4 minutes, other services are really hurting.
> Thanks in advance,
> Weston Weems