Error Expanding Tables In Linked Server To Pervasive
Feb 1, 2005
Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.
I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.
I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:
Provider Name: Pervasive.SQL V8 OLE DB Provider
Product Name: Pervasive.SQL V8
Data source: DemoData
Provider string: <blank>
Location: workstation03
Catalog: <blank>
However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.
Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].
How would I fix this?
Thank you.
View 1 Replies
ADVERTISEMENT
Aug 7, 2006
Hello,
I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :
SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0
SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.
Linked server name : TestPervasive
Provider : Microsoft OLE DB Provider for ODBC Drivers
Product name: Pervasive
Data source: PervasiveDB
Location: PervasiveDB
Catalog : DataDB
I get the following error when I try to query the data to test if the linked server I just created is successful:
query : SELECT * FROM testpervasive.DataDB..schedule
error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TestPervasive" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestPervasive".
Anybody can help me on this one? I would greatly appreciate it !!
View 3 Replies
View Related
Feb 1, 2008
Hi All -
My Set up:
Server A - Oracle 10g Database
Server B - SQL2005
Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted:
{call sp_MSreleasemakegenerationapplock}
Error messages:
The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
View 1 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
May 19, 2015
SQL 2012 version
Microsoft SQL Server 2012 - 11.0.5532.0 (X64)
Jul 14 2014 15:00:27
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
management->SQL Error Log-> list of error files are not expanding,Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
View 8 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Dec 11, 2006
My 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.
View 2 Replies
View Related
Jul 20, 2005
Hiis anybody know how to lin Pervasiva/bitrive SQL server on MS SQL server ?ThanksMarcin Sobolewski
View 1 Replies
View Related
May 25, 2007
Hi there,
Don't know if anyone can help,
There used to be a Whitepaper on the Microsoft website comparing SQL server (2000?) against Pervasive SQL or 2000, the link was http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/.
But it's not working anymore.
Does anyone have a copy of this paper, or know where it's now located? I've been looking for hours and can't find it.
Thanks in advance.
Jezza
View 3 Replies
View Related
Jun 9, 2006
Hi
I have tried to import Pervasive Database to SQL Server 2005.
I am not getting any option in Data Source for Pervasive So I tried following
I have Provided the following Connection String to SQL server Import and Export Wizard (in .net framework data provider for ODBC)
Connectio String: Dsn=DEMODATA;Driver={Pervasive ODBC Engine Interface}
DSN: DEMODATA
Driver:Pervasive ODBC Engine Interface
but i m getting error that "Can not get supported data types from th database connection."
Can you please look in to it and tell me possible ways by which I can import pervasive Database into SQL server 2005.
and let me know if there is any other tool by which we can see "Pervasive ODBC Engine Interface"
in Data Source
Thanks,
Arun
View 18 Replies
View Related
Mar 9, 2004
Hello,
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
Thanks
Josh
View 1 Replies
View Related
Jul 20, 2005
HiI received the below error when trying to run an update from one SQLServer to another.I can insert and select. I cannot delete or update. The permissionshave been changed to allow the linked server user to carry outeverything, the linked servers are working but we cannot change thedata.We are stumpped and your help would be appreciated.Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"charmfin"."charm"."TMP_BATCHPOSTING"' from OLEDB provider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. Nowork was done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...Thanks in advance.Simon
View 1 Replies
View Related
Aug 11, 2007
I just finished up setting up linked server from one of my database machines to another. After futzing to get permissions just right, it works great for many things:
select top 5 * from altai.prep.dbo.simulation_status
0 Prepping Simulation is being prepped by the owner.
1 Prepped Simulation has been prepped by the owner.
2 Checking Simulation is being checked by the owner.
3 Checked Simulation has been checked.
4 Running Simulation is running.
I can even see tables using sp_tables_ex:
sp_tables_ex 'ALTAI', 'S%', 'dbo', 'prep', NULL, 1
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
prep dbo Simulation TABLE NULL
prep dbo Simulation_References TABLE NULL
prep dbo Simulation_SimulationGroup TABLE NULL
prep dbo Simulation_Status TABLE NULL
prep dbo SimulationGroup TABLE NULL
(5 row(s) affected)
The trouble is, I have another table in this database called that begins with "S": Staging_Transition_States. If I try to access this table via the linked server, I get:
select * from altai.prep.dbo.Staging_Transition_States
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "altai" does not contain the table ""prep"."dbo"."Staging_Transition_States"". The table either does not exist or the current user does not have permissions on that table.
This table exists, sp_help for this table is below. Why doesn't this table show up in the output of sp_tables_ex? As far as I can tell, permissions for the tables that work and this table are identical.
Thanks!
--Andrew
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
Staging_Transition_States dbo user table 2007-07-27 11:29:47.790
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
pdb4 char no 4 yes no yes SQL_Latin1_General_CP1_CI_AS
temp smallint no 2 5 0 yes (n/a) (n/a) NULL
run smallint no 2 5 0 yes (n/a) (n/a) NULL
sim_id int no 4 10 0 yes (n/a) (n/a) NULL
time_step int no 4 10 0 yes (n/a) (n/a) NULL
time decimal no 5 9 2 yes (n/a) (n/a) NULL
status tinyint no 1 3 0 no (n/a) (n/a) NULL
order int no 4 10 0 yes (n/a) (n/a) NULL
comment varchar no 250 yes no yes SQL_Latin1_General_CP1_CI_AS
created smalldatetime no 4 yes (n/a) (n/a) NULL
created_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
updated smalldatetime no 4 yes (n/a) (n/a) NULL
updated_by sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
The object 'Staging_Transition_States' does not have any indexes, or you do not have permissions.
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT on column created DF__Staging_T__creat__047AA831 (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column created_by DF__Staging_T__creat__056ECC6A (n/a) (n/a) (n/a) (n/a) (suser_sname())
DEFAULT on column status DF__Staging_T__statu__038683F8 (n/a) (n/a) (n/a) (n/a) ((0))
DEFAULT on column updated DF__Staging_T__updat__0662F0A3 (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column updated_by DF__Staging_T__updat__075714DC (n/a) (n/a) (n/a) (n/a) (suser_sname())
No foreign keys reference table 'Staging_Transition_States', or you do not have permissions on referencing tables.
No views with schema binding reference table 'Staging_Transition_States'.
View 10 Replies
View Related
Jun 29, 2000
Hi,
I am running the follwing query to select data from a local and linked
server using a right outer join as follows and get the error message
shown below the query. How can this be corrected please? sg_q5 is local
and sg_qt1 is defined as a linked server on sg_q5. Non join direct
select commnads from sg_Q5 on the linked server works fine.
>>
Query :
Select name
from sysobjects RIGHT OUTER JOIN sg_qt1.globaldb.dbo.sysobjects
ON sysobjects.dbo.Name = sg_qt1.globaldb.dbo.sysobjects.name
where type = 'u' and name <> 'dtproperties' and name <> 'Rowcounts'
Error Message:
Server: Msg 117, Level 15, State 2, Line 4
The number name 'sg_qt1.globaldb.dbo.sysobjects' contains more than
the maximum number of prefixes. The maximum is 3.
>>
Thanks.
Ranjit
View 2 Replies
View Related
Apr 28, 2004
I am trying to create a linked server to some dbase 5 tables. I have read several other posts about using the OPENROWSET option and that does work however, I would like to be able to create a linked server for easier access.
End Goal: I am developing an ASP based app that needs to join a DB5 table to a SQL table.
Any help on the linked server properties in Enterprise Manager for dbase5 or on the proper sp_addlinkedserver function for dbase5 would be highly appreciated.
Joe
View 2 Replies
View Related
Dec 3, 2007
Reference:
http://support.microsoft.com/kb/306397
http://www.databasejournal.com/features/mssql/article.php/10894_3290801_2
In enterprise manager, I can browse the objects by clicking on "Tables" under the selected linked server, and that allows me to browse my objects in that server and eventually design my query graphically.
In SQL Server 2005 Express, I know that I cannot browse the objects after referring to microsoft article above.
"Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains. "
However, is it possible to design my query graphically?
View 1 Replies
View Related
Sep 2, 2005
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 1827: CREATE/ALTER DATABASEfailed.There's more, but it's in italian and I wouldn't know how to translate itproperly.It basically says that the requested size is exceeding some 2048mb limit.I can't see any limitation of this kind in the license. Is there something Iam missing here?Thank you in advance- andrea
View 3 Replies
View Related
Jun 7, 2000
Hello,
We are currently live with a CRM solution (Siebel) that uses SQL Server 6.5 as the back end. All is fine and dandy, except I have some reservations about security.
Quite simply, it is possible for anyone to open up MS Access and link to any of the SQL Server database tables via the ODBC DSN used by the Siebel front end. This DSN is necessary for Siebel to function.
I am bit worried that someone (out of incompetence or spite) might do just that and cause some serious damage. Its probably technically beyond the large proportion of our users (especially those that could make mistakes!), but I can't get the nagging fear out of my head.
Does anyone know of anyway to combat this problem? I have scoured the web, including this site, and cant seem to get any information on this.
Thanks and Regards
Dike
View 2 Replies
View Related
Mar 23, 2006
is it possible to define in sql server linked tables to odbc data sources as it is possible to do in access?
except Access and vfp, is there another database that allows to create linked tables to odbc data sources?
I need to combine in a database native tables with odbc linked tables, but it would need to use a database more powerful that access or vfp
View 2 Replies
View Related
Jul 23, 2005
I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
View 1 Replies
View Related
Jul 23, 2015
I am attempting to reach some Clipper tables through a 32-bit ODBC driver from a 64 bit SQL Server. As there is no 64 bit driver offered for Clipper, I am pursuing a solution similar to the one described here:
Creating a Linked Server with 64 bit SQL Server 2008 to MS Access
It involves using a SQL Express 32 bit instance as a bridge.
I have created a Linked Server on the 32 bit instance MTESTXPRESS as follows:
EXEC sp_addlinkedserver @server = N'ABDATA', @srvproduct=N'DataDirect 4.1', @provider=N'MSDASQL', @datasrc=N'ABServerCA'
On the 64 bit instance ALISTESTER I have another Linked Server as follow:
EXEC sp_addlinkedserver @server = N'ABACUS', @provider=N'SQLNCLI', @datasrc=N'ALISTESTERMTESTEXPRESS'
The suggestion is to then use a select statement such as:
SELECT * FROM OPENQUERY(ABACUS, 'SELECT COUNT(*) FROM ABDATA...ABBATCH')
Unfortunately, the DataDirect driver for MTESTEXPRESS will not recognize the 'ABDATA...ABBATCH' 3-part naming convention. The error message is:
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "ABDATA"
Is there some other way to select from the MTESTEXPRESS linked server?
View 21 Replies
View Related
Jun 4, 2007
Hello,
I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.
For some tables, it return all data but for another tables, it only return part of the rows.
How it may happen?
Thanks
View 1 Replies
View Related
May 5, 2008
Hi Guys:
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
Thank you.
View 4 Replies
View Related
Oct 1, 2014
I work in Healthcare IS for Company A, but Company B is hosting one of our EMR programs for us. This was done on purpose, so that whether a patient is seen at one or the other, their medical history is more complete. However, this puts all of the data that I need to get to on a server across town that I can only access via Sql Server Management Studio as a linked server.
Now, in some ways, the performance has been better than I expected, but sometimes it behaves very erratically. I am using OPENQUERY to handle all of the pulls, and am not joining to any local tables, in order to maximize efficiency.
Here is some of the code I run, and what happens:
SELECT *
FROM
OPENQUERY([linkedservername],
'
SELECT *
FROM Encounter_ItemChild
[Code] ....
***The above query was programmatically generated by taking the IDs from the second query, and packing as many into the IN condition as possible. Each statement could hold only about 900 IDs, so around 70 queries get built...however, each one returns the records in question in 1-2 seconds.
My main question is...if the second query pulls all IDs from Encounter in a few seconds, and that query is used in the first query's WHERE clause, why does it spin and spin, while manually throwing the IDs in instead runs almost instantly?
View 3 Replies
View Related
Nov 10, 2014
I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?
View 4 Replies
View Related
Mar 13, 2007
I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.
I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.
What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.
I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.
It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?
Thanks in advance for any help on this
View 1 Replies
View Related
Mar 2, 2007
1. Replication Transactional between two servers
-- SQL Server 2005 as Distributors and Subscribers
---SQL Server 2000 as Publishers
2. Linked Servers errors:
" Server (Publication Server) is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)"
Did anyone familiar with this problem?
Thankssss
TJ_1
View 4 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jul 20, 2005
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies
View Related
Apr 18, 2008
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
View 5 Replies
View Related
Mar 8, 2001
could someone please explain the following error msg! the code seg that is gen the error is as follows, followed by the err msg. I"m trying to update a table on a linked server (paeddb1.gold). both servers are running MS SQL7
update paeddb1.gold.dbo.controls_peg
set amt = t.amt
from #temp_peg_control t, paeddb1.gold.dbo.controls_peg p
where t.peg = p.peg
and t.cntl_type = p.cntl_type
and t.fy = p.fy
Could not open table '"gold"."dbo"."controls_peg"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Errors occurred]
View 2 Replies
View Related
Sep 19, 2005
I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table
SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table
Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'
Thanks!
Josh
View 9 Replies
View Related
Apr 10, 2008
Hi Pals,
We have an SSIS package within which we are calling a stored procedure which eventually call a sql server dbo.fn() which contains code to lookup data inside oracle using Linked Server for Oracle.
We are calling the Package dynamically from the stored procedure by creating a SQL Server Agent Job, I am getting the below error very often. Can we fix the error in any way?
“EXEC sp_UpdateTname 369,'939390',2008 " failed with the following error: "Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORATEST".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
When I hard code global variables and ran the package I am able to see the successful execution of the package.
Can anybody point out where could the error lies?
View 1 Replies
View Related
