Has anyone successfully imported data from pervasive over the .net managed odbc bridge? Our admin has set up a odbc connection that works through Excel, but in SSIS the connection manager errors when trying to connect at runtime (the "test connection" button in the connection wizard reports that everything is ok though).
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!
I'm using the import/export wizard and are trying to connect to another systems database Views and getting the following error
Cannot get the supported data types from the database connection “Dsn = TMS Live:Driver ={JADE ODBC Driver 6.1}
Additional information:
Unknown SQL type – 128.(system.Data)
I think the problem is that the system i'm trying to connect to is an object based system rather than relational based, hence having to import the views rather than tables
I am able to connect using the same DSN/driver in DTS SQL2000 which we currently use, but want to be able to move to 2005.
Any ideas on how i can connect?
Cheers
Did anyone try fetching data from any database which is connected through ODBC. If so can you please share the details. In the data flow task of SSIS there is a facility for only oledb and not thru ODBC.
Hi,
I'm new about SQL Server 2005 Express, The instalation was with no problems. However when I try to connect through an ODBC, I get this error:
Microsoft SQL Native Client Version 09.00.3042
Running connectivity tests...
Attempting connection
[Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
TESTS FAILED!
Migth you help me please?
Regards
Hi,
I am trying to connect SQL server7.0 through ODBC 3.5,when i tried to create DSN ,i am getting below error message.I added alias with server name as IP Address in client network utility,But no use.I tried to connect through query analyzer but no use. ( Client M/c and Sql Servers are on T1 network)
** But i am able to connect through ISQL
I am not understanding why i am not able to connect through ODBC and query analyzer.Can some one help me in this.
ERROR MESSAGE:
****************************************
unable to connect to the server {....IP Address....}. odbc:Msg 0, Level 16, State 1 [Microsoft][ODBC SQL Server Driver]Timeout expired
******************************************
Thanks
Ananth
"""
I was wondering if anyone knows how to connect with SQL Server over TCP/IP using ODBC
I tried using the IP address or domain name of the remote machine, but I get the following error:
Microsoft SQL Server ODBC Driver Version 03.70.0690
Running connectivity tests...
Attempting connection
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
TESTS FAILED!
Thanks in advance
Mohamed
I'm currently trying to access data from a Cache DB using MSSQL. I havelinked the Cache server through an ODBC connection. I can see in the LinkedServerexpansion all the tables in Cache for the File(?) I want to access.Here is my problem: Normally to access a linked sever I would do thefollowing:select *from ServerName.DatabaseName.dbo.TableNameI have triedselect *from ServerName.DSN.dbo.TableNameselect *from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)None have worked (error no such object...)What is the syntax to select data on a linked sever via an ODBC connection?In the Linked server set up I have also check the RPC and RPC Out options...My ultimate goal is join tables in Cache and MSSQL into MSSQL.Any help would be greatly appreciated!Thanks,-p
View 2 Replies View RelatedI need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but
when I exchange server roles, connect fails (the error message is:
Cannot open database "TestDB" requested by the login. The login failed.
in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=test;Password=test;Failover Partner=10.0.1.162;Initial
Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with
column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string
Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover
Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks
I have an SQL2012instance running.
I create a sql user who is part of sysadmin, securityadmin, setupadmin and serveradmin roles.
When I try to connect through odbc using this user from other machines, it works fine. But if I remove it from sysadmin, I get an error message Connection Failed:
SQLState : '28000'
SQL Server Error: 18456
[Microsoft][SQL Server Native Client 11.0][SQL Server][Login Failed for user:user1]
Hi
I have a server with SQL 2005 and I have second server with spesial database program on it from IBM and I have installed a spesial ODBC DRIVER from IBM so I can make a connection in system dns.
On my server 1 where I have the sql 2005 I have made a connection in system dns.
HOW CAN I TELL SQL 2005 TO CONNECT TO MY CONNECTION AND GET DATA FROM DER??????
I have an SQL2012instance running.
I create a sql user who is part of sysadmin, securityadmin, setupadmin and serveradmin roles.
When I try to connect through odbc using this user from other machines, it works fine. But if I remove it from sysadmin, I get an error message
Connection Failed:
SQLState : '28000'
SQL Server Error: 18456
[Microsoft][SQL Server Native Client 11.0][SQL Server][Login Failed for user:user1]
Hi,
Am new to SQL Server 2005...actually this is my first day with it
am trrying to connect to SQL Server which resides on a 64-bit machine from a 32-bit machine using ODBC with a DSN aliasing thing
it gives me error msg "login failed for user XXX" with error state indicating that password missmatch or sometimes invalid user!!
i dont use the password i keep it blank and checked everything i could possibly think of...
regards,
I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :
Data source name not found and no default driver specified
The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.
Thanks
Hello All,
Does anybody know how to achive that?
<add name="ConnectionString" connectionString="Dsn=dsn_name;uid=user;pwd=password" providerName="System.Data.Odbc" />
I have got this created by the wizard for my sqlDataSource. I am able to get data from tables in the default database specified with the DSN.
But I want to go to other database than the default.
Thanks a lot in advamce for your help!
Kazu
I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:
Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener
ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.
I have tried OLEDB connection and that does not work either.
Any inputs will be appreciated.
Thanks,
Monisha
Hi!
I am trying to establish connection from different domain using ODBC driver with no luck!
I am able to do it within my domain from any place, but it fails from outside. I have two-ways trust established between domains. Users are able to see and use resources both ways.
When I create ODBC it doesn't matter if I use SQL or Windows NT authenctication, I am getting error:
Connection Failed:
SQLState: '01000'
SQLServer Error: 11004
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection Open (getbyhostname()() )
Connectio Failed:
SQLState: '08001'
SQL Server Error: 11
I tried to set it with TCP Static Port or Dynamic. No difference.
What should I look into?
Any ideas? Any help?
Thanks.
Hi,
Anybody have a clear idea why I might have problems connecting from reporting services to Oracle 9i database using the Microsoft ODBC driver? Getting following error
"Cannot create a connection to data source 'oracleName'.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
"
I have the data source correctly set-up thought. Is it a permissions problem?
Thanks.
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
Hi,
Is it possible to run MS SQL 7 on the same server with Pervasive SQL?
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
I've a 32 bits application that runs on 64 bits Windows Server+MS-SQL.
I've created an ODBC DSN using c:WindowsSysWow64odbcad32.exe.
Sp, the program recognize the ODBC but having problem connecting.
The error look likes this:
***
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '<name>' in sys.server. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
***
Any help is appreciated.
Regards,
Ben
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Any assistance would be appreciated.
Thanks,
I am using Maximizer Enterprise 8.0 as my companies CRM solution. Currentlywe are using the included Pervasive SQL that shipps with the product. Isthere someone out that who could tell me if there are any performancebenefits to switching the Pervasive Engine with a Microsoft SQL engine.RegardsJesus
View 5 Replies View RelatedMy company is trying to determine if they need to upgrade from our current Platinum accounting application that uses pervasive SQL V8 to another version of Platinum that uses SQL server 2000.
My manager wants a in-depth analysis comparing pervasive SQL V8 to SQL server 2000.
I have searched the web extensively but information is scarce.
Are there any articles or case studies that do an indepth analysis comparing both databases? Can someone please help me. It would be greatly appreciated.
How can i format date in pervasive sql?
The day in my table is stored as "yyyymmdd"
I want to search for records with curdate().
select Curdate() returns 'dd/mm/yyyy'
Any help will be appreciated.
Thanks in advance
I am trying to write a stored procedure for the first time. I am using pervasive 9.0. I am tying together a secure desktop messaging system with a web application. I will be passing the data that matches what the desktop app needs to fill in the tables.
The issue is that the desktop app uses tow tables to do messaging. One table (PDSMSGC) that stores the relevant information about the message: sender, recipient, notes, phone number, etc. The other table (MEMOS) stores the message text and references back to the "PDSMSGC" table (The memos table in the desktop app is used for more than just the messaging system.)
So both tables have unique IDs that are referenced in both tables. For example:
MEMOS.ParentID = PDSMSGC.MessageID
PDSMSGC.MemoID = MEMOS.MemoID
here are the column definitions:
My question is: Does this seem like a properly formed set of commands and can you think of a better way to do this?
Here is what I have written so far:
CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);
BEGIN
DECLARE :NewMemoID UINTEGER;
DECLARE :NewMessageID UINTEGER;
INSERT INTO PDSMSGC (ToOper, MsgFrom, LinkID, PrimaryID, Phone, MemoID, DateTaken, TimeTaken, TakeOper, BeenRead) VALUES (:RecipientID, :PatientName, '2', :PatientID, :PatientPhone, :PatientID, CurDate(), CurTime(), 'WEB', '5');
INSERT INTO MEMOS (ParentID, FieldName, MemoText) VALUES (:PatientID, 'Xholder', :MemoText);
SELECT :NewMemoID = MemoID FROM MEMOS WHERE MEMOS.ParentID = :PatientID AND MEMOS.FieldName = 'Xholder' AND MEMOS.MemoText = :MemoText;
SELECT :NewMessageID = MessageID FROM PDSMSGC WHERE PDSMSGC.MsgFrom = :PatientName AND PDSMSGC.PrimaryID = :PatientID AND PDSMSGC.MemoID = :PatientID AND PDSMSGC.TimeTaken = CurDate() AND PDSMSGC.TakeOper = 'WEB' AND PDSMSGC.BeenRead = '5';
UPDATE MEMOS SET ParentID = :NewMessageID, FieldName = 'pmc:MemoID', MemoText = :MemoText WHERE MEMOS.MemoID = :NewMemoID;
UPDATE PDSMSGC SET ToOper = :RecipientID, MsgFrom = :PatientName, MemoID = :NewMemoID, BeenRead = '0' WHERE PDSMSGC.MessageID = :NewMessageID;
END;
Hi All,Thanks in advance for the help.We are considering a new Project. This project is going to be totallyweb based.Currently my company is using Pervasive SQL 2000i for there ERP needs.Will the existing Pervasive SQL 2000i itself serve the purpose ORshould I consider moving to MS SQL Server.Do assist me in terms of development also i.e. Is it easier to codewith Pervasive SQL 2000i or with MS SQL Server.RegardsSamson
View 1 Replies View RelatedHiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski
View 1 Replies View RelatedIs it possible to run MSDE and Pervasive SQL on the same machine ?
If possible but not recommended I'd really appreciate the reasons why it should not be done.
If not possible I'd also really appreciate hearing why.
Thanks in advance.
In SQL 2000, I had a working DTS package that would import a Pervasive SQL database into SQL 2000 (There is a good reason, provided on request). The column type definitions came over just fine in SQL 2000 with a few minor changes.
In SQL 2005 (SSIS), i create a Data Source via the Connection Manager (Provider: .Net ProvidersOdbc Data Provider) to the Pervasive database (System DSN, <database_odbc>). I then create a Data Destination via the Connection Manager (Provider: Native OLE DBSQL Native Client) to the SQL database. Both databases reside on the same machine.
I've created a DataSource Reader and used the sql command, "select * from ARCustomer" as an example. The issue is with "data types" for the columns. They don't come close to resembling the results that i had in SQL 2000 DTS.
Is there another method or parameter setting that will preserve the "data types" for the columns being imported from the Pervasive database.
This has been a real stumbling block and any help would be truly appreciated. Thanks in advance for your assistance ... Bernie
Hi all, I currently have a project that I need to export Pervasive SQL into SQL 2005 database and I don't know what tool or steps that need to do this. Is there anyone can help me on this?
Thanks
We just upgraded our accounting packages database from Pervasive 8.1 to V9.11. MY SSIS extract packages were working just fine. Now I am getting several erros stating that some columns cannot be found:
For example, in the [sysdtslog90] table, I have these errors:
Column "CreditLimitAmt" cannot be found at the datasource. or
Column "RsrvRptky" cannot be found at the datasource. (from a different pkg)
The table schemas did not change, the columns still exist. The SQL I use also runs just fine in the client interface.
Has anyone experienced this? The bottom line is that now the extract packages are broken.
TIA.