Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts

Friday, March 30, 2012

Problem with linked servers

Hi,
we are trying to get some data from a linked server via an ODBC connection.
This server has 3 intances of the same software, we load 2 tables from each
installation. From one instance we get both tables, from the two others we
can only acces one of the two.
I explain that only because I want to make clear the the ODBC driver
"normally" should be able to be used with Linked servers.
When we try to load the two other tables, we get an error 7317:
OLE/DB provider returned an invalid schema definition
The ODBC driver is based on the SIMBA ODBC framework.
Any idea what I can do? What's about the "schema definition"?
Thanks,
Thomas1. Can you do simple select like "SELECT * from
servername.dbname.owner_name.table_name"
2. Does the account that has been used in defining the linked server has
proper security on the linked server tables.
"Thomas Pagel" <tpagel@.software4you.com> wrote in message
news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we are trying to get some data from a linked server via an ODBC
connection.
> This server has 3 intances of the same software, we load 2 tables from
each
> installation. From one instance we get both tables, from the two others we
> can only acces one of the two.
> I explain that only because I want to make clear the the ODBC driver
> "normally" should be able to be used with Linked servers.
> When we try to load the two other tables, we get an error 7317:
> OLE/DB provider returned an invalid schema definition
> The ODBC driver is based on the SIMBA ODBC framework.
> Any idea what I can do? What's about the "schema definition"?
>
> Thanks,
>
> Thomas
>|||Ammar,
1.) This works with some of the tables, others don't
2.) The queries work well if I direclty use the ODBC driver i.e. with DTS
with the same user and it has all rights, too. So this couldn't be a
security issue...
Thanks,
Thomas
"Ammar" <ammar.ansari@.anthem.com> schrieb im Newsbeitrag
news:%23CeduY4VDHA.2544@.tk2msftngp13.phx.gbl...
> 1. Can you do simple select like "SELECT * from
> servername.dbname.owner_name.table_name"
> 2. Does the account that has been used in defining the linked server has
> proper security on the linked server tables.
>
> "Thomas Pagel" <tpagel@.software4you.com> wrote in message
> news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we are trying to get some data from a linked server via an ODBC
> connection.
> > This server has 3 intances of the same software, we load 2 tables from
> each
> > installation. From one instance we get both tables, from the two others
we
> > can only acces one of the two.
> >
> > I explain that only because I want to make clear the the ODBC driver
> > "normally" should be able to be used with Linked servers.
> >
> > When we try to load the two other tables, we get an error 7317:
> >
> > OLE/DB provider returned an invalid schema definition
> >
> > The ODBC driver is based on the SIMBA ODBC framework.
> >
> > Any idea what I can do? What's about the "schema definition"?
> >
> >
> > Thanks,
> >
> >
> > Thomas
> >
> >
>

Wednesday, March 21, 2012

problem with group by

hello all,

i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

this my scripts

$query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
$query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
$query .= " ticket.t_summary, ticket.t_category, ";
$query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
$query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
$query .= " FROM events, tmpeid, category, ticket ";
$query .= " WHERE ticket.t_id = events.t_id ";
$query .= " AND events.e_id=tmpeid.e_id";
$query .= " GROUP BY events.t_id";
$query .= " HAVING COUNT(events.t_id) >= 1 ";

this is error msg that i've found

Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

can anybody solve for me???Hello,

the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.
f.e

SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
(also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

(will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)

a

SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

(works also fine)

So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

or ...

group every field in the list

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.comsql

Friday, March 9, 2012

problem with duplicate keys

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Thank you for any suggestions.

Pawel BanysOn Thu, 5 Feb 2004 09:13:28 +0100 in comp.databases.ms-sqlserver,
"Pawel Banys" <voland@.dmz.com.pl> wrote:

>Hello,
>There is a program which performs some scripted actions via ODBC on tables
>in some database on mssql 2000. Sometimes that program tries to insert
>record with key that is already present in the database. The error comes up
>and the program stops.
>Is there any way to globally configure the database or the whole mssql
>server to ignore such attempts and let the script continue without any error
>when the script tries to insert duplicate-key records?

Maybe you should change your script to check for the keys first,
instead of blindly whacking stuff into them e.g.

insert into table1 (pk, column)
select pk, column from table2
where table1.pk not in (select pk from table2)

--
A)bort, R)etry, I)nfluence with large hammer.|||"Pawel Banys" <voland@.dmz.com.pl> wrote in message news:<bvsu0g$3fp$1@.atlantis.news.tpi.pl>...
> Hello,
> There is a program which performs some scripted actions via ODBC on tables
> in some database on mssql 2000. Sometimes that program tries to insert
> record with key that is already present in the database. The error comes up
> and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any error
> when the script tries to insert duplicate-key records?
> Thank you for any suggestions.
> Pawel Banys

Are you talking about error 2627 (violation of primary key
constraint)? If so, then the current batch should continue anyway,
unless of course the code has error handling to stop immediately on an
error.

Assuming you have control of the code, you should really avoid the
error instead:

insert into dbo.Destination (col1, col2, ...)
select col1, col2, ...
from dbo.Source s
where not exists (select * from dbo.Destination d
where d.PrimaryKey = s.PrimaryKey)

If you don't control the code, then I don't think there's much you can
do - the error can't be suppressed, and needs to be handled on the
client. Perhaps you can clean up the data somehow before passing it to
the program?

Simon|||Pawel Banys (voland@.dmz.com.pl) writes:
> There is a program which performs some scripted actions via ODBC on
> tables in some database on mssql 2000. Sometimes that program tries to
> insert record with key that is already present in the database. The
> error comes up and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any
> error when the script tries to insert duplicate-key records?

For a unique index - but not a PK or UNIQUE constraint - you can include
the option IGNORE_DUP_KEY. With this setting, SQL Server does not find
it an error condition if you try to insert a duplicate, but leaves @.@.error
unchanged. Unfortunately, though, the message that is raised, incorrectly
has severity level 16 and not 10, so a client may still belive that
things went wrong.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.

insert into server2.database.dbo.table select * from
server1.database.dbo.table

Adding a where clause would take much MUCH longer to perform vs. a
straight insert.

I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?

================
Jordan Bowness
================

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jordan Bowness (jb_at_bizeworld_dot_ca) writes:
> I too would like to be able to have it ignore errors thrown on an
> insert. I'm doing the insert over a WAN, so checking for all records on
> the destination server takes forever.
> insert into server2.database.dbo.table select * from
> server1.database.dbo.table
> Adding a where clause would take much MUCH longer to perform vs. a
> straight insert.
> I've been deleting all records from the destination table, but that's
> not a great way of doing it. Any way of having the insert simply ignore
> errors and continue?

There is the option that I discussed in the article you replied to. That
is, replace the primary key with a unique index with IGNORE_DUP_KEY.

Another possibility is to insert the data into a staging table on the
target server, and then call a stored procedure on the target server
which performs an INSERT targettbl SELECT * FROM staging WHERE NOT EXISTS.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm having a similar issue here, i'm using a Insert Trigger to add a row to an EXCEPTIONS table when a condition is met during the insert process. The condition could be met multiple times, but I only need 1 record to go into the EXCEPTIONS table per PK combination. I could limit this using a WHERE clause however the EXCEPTIONS table has a composite primary key and I'm not sure how to check for that.

INSERT INTO STAFFING_EXCEPTIONS ...
SELECT ...
FROM INSERTED I

INNER JOIN STAFFING_3W_REQ REQ ON (I.PROJECT_CODE = REQ.PROJECT_CODE AND I.LOB_CODE = REQ.LOB_CODE AND I.ROW_DATETIME = REQ.ROW_DATETIME AND I.FTE_GOAL != REQ.FTE_GOAL)

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony