Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts

Wednesday, March 28, 2012

Problems with SQL Server 2005 Oracle Linked Server

I am experiencing numerous problems with a SQL Server 2005 Linked Server for
Oracle. The first and most pressing issue is when executing the following
statement:
Select * from openquery( {Oracle Linked Server}, 'select * from
target_table;')
Using the above query the results set in SSMS is only a subset of all the
rows that are actually in the table.
For example if there are 200 rows in the table, the above query will only
return 150 rows. Although, it should return all the rows in the table. At
the same time I can execute the following query and get the correct row
count:
Select * from openquery( {Oracle Linked Server}, 'select count(*) from
target_table;')
I am using Microsoft OLE DB Provider for ODBC Drivers with a data source set
up using ODBC Administrator. I have installed Oracle 9i client tools. I
have attempted a linked server using Oracle Provider for OLE DB to no avail.
The linked server using Oracle Provider for OLE DB returns the following
error:
OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
returned message "ORA-00911: invalid character".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select count(*) from
{Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle" for
linked server "{Linked Server}".
Any help with this issue would be greatly appreciated.
Thandi Turner"Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linked
Server@.discussions.microsoft.com> wrote in message
news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
>I am experiencing numerous problems with a SQL Server 2005 Linked Server
>for
> Oracle. The first and most pressing issue is when executing the
> following
> statement:
> Select * from openquery( {Oracle Linked Server}, 'select * from
> target_table;')
> Using the above query the results set in SSMS is only a subset of all the
> rows that are actually in the table.
> For example if there are 200 rows in the table, the above query will only
> return 150 rows. Although, it should return all the rows in the table.
> At
> the same time I can execute the following query and get the correct row
> count:
> Select * from openquery( {Oracle Linked Server}, 'select count(*) from
> target_table;')
> I am using Microsoft OLE DB Provider for ODBC Drivers with a data source
> set
> up using ODBC Administrator. I have installed Oracle 9i client tools. I
> have attempted a linked server using Oracle Provider for OLE DB to no
> avail.
> The linked server using Oracle Provider for OLE DB returns the following
> error:
> OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
> returned message "ORA-00911: invalid character".
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query "select count(*) from
> {Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle"
> for
> linked server "{Linked Server}".
>
You need to be using the OLE DB Provider from Oracle.
The error
ORA-00911: invalid character
means exactly what it says.
In Oracle SQL ';' is an invalid character. It is the statement terminiator
in PL/SQL, but is forbidden in Oracle SQL.
David|||Thanks, but removing the semi-colon eliminated the error but still did not
return the full result set. Still need help in getting all of the rows back
regardless of the provider.
"David Browne" wrote:
> "Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linked
> Server@.discussions.microsoft.com> wrote in message
> news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
> >I am experiencing numerous problems with a SQL Server 2005 Linked Server
> >for
> > Oracle. The first and most pressing issue is when executing the
> > following
> > statement:
> >
> > Select * from openquery( {Oracle Linked Server}, 'select * from
> > target_table;')
> >
> > Using the above query the results set in SSMS is only a subset of all the
> > rows that are actually in the table.
> >
> > For example if there are 200 rows in the table, the above query will only
> > return 150 rows. Although, it should return all the rows in the table.
> > At
> > the same time I can execute the following query and get the correct row
> > count:
> >
> > Select * from openquery( {Oracle Linked Server}, 'select count(*) from
> > target_table;')
> >
> > I am using Microsoft OLE DB Provider for ODBC Drivers with a data source
> > set
> > up using ODBC Administrator. I have installed Oracle 9i client tools. I
> > have attempted a linked server using Oracle Provider for OLE DB to no
> > avail.
> > The linked server using Oracle Provider for OLE DB returns the following
> > error:
> >
> > OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
> > returned message "ORA-00911: invalid character".
> > Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing the query "select count(*) from
> > {Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle"
> > for
> > linked server "{Linked Server}".
> >
> You need to be using the OLE DB Provider from Oracle.
> The error
> ORA-00911: invalid character
> means exactly what it says.
> In Oracle SQL ';' is an invalid character. It is the statement terminiator
> in PL/SQL, but is forbidden in Oracle SQL.
> David
>
>|||"Problems w/ SQL2K5 Oracle Linked Server"
<ProblemswSQL2K5OracleLinkedServer@.discussions.microsoft.com> wrote in
message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> Thanks, but removing the semi-colon eliminated the error but still did not
> return the full result set. Still need help in getting all of the rows
> back
> regardless of the provider.
>
What version of the Oracle client and what version of Oracle?
David|||Thanks, David. You definitely got the ball rolling. After being able to
execute the query with the Oracle Provider for OLE DB I was still only
receiving a subset of the total rows in most of the tables. Then the
breakthrough, after changing all the server options to "True" in the link
server properties everything work perfectly.
Thanks, Thandi
"David Browne" wrote:
> "Problems w/ SQL2K5 Oracle Linked Server"
> <ProblemswSQL2K5OracleLinkedServer@.discussions.microsoft.com> wrote in
> message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> > Thanks, but removing the semi-colon eliminated the error but still did not
> > return the full result set. Still need help in getting all of the rows
> > back
> > regardless of the provider.
> >
> What version of the Oracle client and what version of Oracle?
> David
>
>sql

Problems with SQL Server 2005 Oracle Linked Server

I am experiencing numerous problems with a SQL Server 2005 Linked Server for
Oracle. The first and most pressing issue is when executing the following
statement:
Select * from openquery( {Oracle Linked Server}, 'select * from
target_table;')
Using the above query the results set in SSMS is only a subset of all the
rows that are actually in the table.
For example if there are 200 rows in the table, the above query will only
return 150 rows. Although, it should return all the rows in the table. At
the same time I can execute the following query and get the correct row
count:
Select * from openquery( {Oracle Linked Server}, 'select count(*) from
target_table;')
I am using Microsoft OLE DB Provider for ODBC Drivers with a data source set
up using ODBC Administrator. I have installed Oracle 9i client tools. I
have attempted a linked server using Oracle Provider for OLE DB to no avail.
The linked server using Oracle Provider for OLE DB returns the following
error:
OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
returned message "ORA-00911: invalid character".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select count(*) from
{Target_Table};" for execution against OLE DB provider "OraOLEDB.Oracle
" for
linked server "{Linked Server}".
Any help with this issue would be greatly appreciated.
Thandi Turner"Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linked
Server@.discussions.microsoft.com> wrote in message
news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
>I am experiencing numerous problems with a SQL Server 2005 Linked Server
>for
> Oracle. The first and most pressing issue is when executing the
> following
> statement:
> Select * from openquery( {Oracle Linked Server}, 'select * from
> target_table;')
> Using the above query the results set in SSMS is only a subset of all the
> rows that are actually in the table.
> For example if there are 200 rows in the table, the above query will only
> return 150 rows. Although, it should return all the rows in the table.
> At
> the same time I can execute the following query and get the correct row
> count:
> Select * from openquery( {Oracle Linked Server}, 'select count(*) fro
m
> target_table;')
> I am using Microsoft OLE DB Provider for ODBC Drivers with a data source
> set
> up using ODBC Administrator. I have installed Oracle 9i client tools. I
> have attempted a linked server using Oracle Provider for OLE DB to no
> avail.
> The linked server using Oracle Provider for OLE DB returns the following
> error:
> OLE DB provider "OraOLEDB.Oracle" for linked server "{Linked Server}"
> returned message "ORA-00911: invalid character".
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query "select count(*) from
> {Target_Table};" for execution against OLE DB provider "OraOLEDB.Orac
le"
> for
> linked server "{Linked Server}".
>
You need to be using the OLE DB Provider from Oracle.
The error
ORA-00911: invalid character
means exactly what it says.
In Oracle SQL ';' is an invalid character. It is the statement terminiator
in PL/SQL, but is forbidden in Oracle SQL.
David|||Thanks, but removing the semi-colon eliminated the error but still did not
return the full result set. Still need help in getting all of the rows back
regardless of the provider.
"David Browne" wrote:

> "Problems w/ SQL2K5 Oracle Linked Server" <Problems w/ SQL2K5 Oracle Linke
d
> Server@.discussions.microsoft.com> wrote in message
> news:0F1603EE-0DEE-47B4-8F8A-40F16BE0FA6E@.microsoft.com...
> You need to be using the OLE DB Provider from Oracle.
> The error
> ORA-00911: invalid character
> means exactly what it says.
> In Oracle SQL ';' is an invalid character. It is the statement terminiato
r
> in PL/SQL, but is forbidden in Oracle SQL.
> David
>
>|||"Problems w/ SQL2K5 Oracle Linked Server"
< ProblemswSQL2K5OracleLinkedServer@.discus
sions.microsoft.com> wrote in
message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> Thanks, but removing the semi-colon eliminated the error but still did not
> return the full result set. Still need help in getting all of the rows
> back
> regardless of the provider.
>
What version of the Oracle client and what version of Oracle?
David|||Thanks, David. You definitely got the ball rolling. After being able to
execute the query with the Oracle Provider for OLE DB I was still only
receiving a subset of the total rows in most of the tables. Then the
breakthrough, after changing all the server options to "True" in the link
server properties everything work perfectly.
Thanks, Thandi
"David Browne" wrote:

> "Problems w/ SQL2K5 Oracle Linked Server"
> < ProblemswSQL2K5OracleLinkedServer@.discus
sions.microsoft.com> wrote in
> message news:1E41CFD4-9A3E-461F-8B1D-2E3D7BF3F711@.microsoft.com...
> What version of the Oracle client and what version of Oracle?
> David
>
>

Friday, March 9, 2012

Problems with executing ssis package by job

Hi All,
I have created ssis package and am trying to execute it using sql
server agent job.
The problem is, that the job failes to find or load the package. It
tries to run it from the server side and failes to find it. I don't
really understand how can I publish the package on the server from the
client side.
Thanks in advance.Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||amish wrote:
> Hi
> Have you saved your package in SQL Server ?
> from file menu in Microsoft Visual Studio select Save copy of package
> as and save your package in SQL Server.
> or
> Connect to intergation service in Management studio and import package.
>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Regards
> Amish Shah
Hi, thanks for replying,
Yes I tried both things, what I get is
Executed as user: CLUSTERNET\admin. The package could not be loaded.
The step failed.
Yours Qwert

Saturday, February 25, 2012

Problems while attaching a database in SQLServer 2000

Dear all,

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||

Hi Deepak,

could you post the t-sql used !!! and post Errors from SQL Error Logs too!!

Hemantgiri S. Gowami

|||

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

|||

How I can solve the error when attaching a database in sqlserver 2000.

Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

Problems while attaching a database in SQLServer 2000

Dear all,

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||

Hi Deepak,

could you post the t-sql used !!! and post Errors from SQL Error Logs too!!

Hemantgiri S. Gowami

|||

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

|||

How I can solve the error when attaching a database in sqlserver 2000.

Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

Problems while attaching a database in SQLServer 2000

Dear all,

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||

Hi Deepak,

could you post the t-sql used !!! and post Errors from SQL Error Logs too!!

Hemantgiri S. Gowami

|||

I recive a error when i attaching a database in sqlserver 2000. The error is :-

An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)

|||

How I can solve the error when attaching a database in sqlserver 2000.

Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.

Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)