Create Relationship With Tables In A Linked Server

Jul 23, 2005

I need to create a relationship between a local table and tables on a
linked server. I used the design table wizard and selected the
relationship property wizard. In the reslationship property wizard,
the tables that I need to get the keys from in the linked server do not
show up. Is there a way to do this, or I simply don't have enough
permission to tables in the linked server. On the local server, the
Security tab of linked server property has Local Loging "sa", Remote
User "sa" and Remote Password "****". Thanks for your help.

View 1 Replies


ADVERTISEMENT

SQL Server 2014 :: Create Auto Relationship Between Tables

May 12, 2014

I would like to create a auto relationship between tables.

Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)

Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)

Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.

What is the approach?

View 9 Replies View Related

How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?

Mar 25, 2006

How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?

For example:
I have 2 tables, tbl1 and tbl2.

tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}

tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id


If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?

mradlmaier

View 3 Replies View Related

How T Create Relationship B/w Two Tables

Apr 9, 2007

Hi
I have two database as Malathi,Indoo
In Malathi database i have

Employee Table AS
----------------------

Eid Int (PK)
Enam varchar(50)


In Indoo Database I have

Job table as

Eid int (FK)
Jid Int (PK)


Now how can i create relation b/w two tables of different database
Any One can help for this query

Malathi Rao

View 3 Replies View Related

How To Create A Datagrid For Two No Relationship Tables

Jan 13, 2004

Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :

ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID

Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID


May I know is there solution for it?

Thank you very much!

View 4 Replies View Related

Transact SQL :: How To Create A 0-Many Relationship Between Two Tables

May 25, 2015

I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.

View 6 Replies View Related

SQL 2012 :: Create View From Multiple Tables That Have 1 To M Relationship

Jul 31, 2014

I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.

for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score

and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error

Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );

I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.

View 5 Replies View Related

Reporting Services :: How To Create 2 Tables With Primary / Foreign Key Relationship

Jun 6, 2015

I want to create a table with primary key , and put relationship with second table.

View 5 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

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

Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource

Jun 5, 2007

Hai,

I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.

The error message is "Invalid Argument".

I use the following code for creating the linked table

Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl


End With

'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")

'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)

'Append the primary index to table.
catDB = Nothing

End Sub

If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.

Thanks in advance
Sudeep T S

View 4 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

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

Failing To Make Relationship Between To Tables Of SQL Server DB

Apr 27, 2004

Hi,

I'm trying to make relationship between two tables "reservation" and "charges". The column is "booking_ticket". Its giving me following error :


'reservations (akr)' table saved successfully
'charges (akr)' table
- Unable to create relationship 'FK_charges_reservations'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_charges_reservations'. The conflict occurred in database 'limp', table 'reservations', column 'booking_ticket'.


I used to make relationship before , but never found this problem.


Kindly guide me to solve it.

Regards,

View 1 Replies View Related

Relationship Between Two Tables In Seperate Databases (on The Same Server)

Mar 20, 2008

Hello,

I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?

Thanks,

Mark

View 10 Replies View Related

Retrieving The Tables Relationship From SQL Server Database Diagrams

May 26, 2004

Hi All,

Currently i am defining a simple relationship between
Customers->Orders->Order Details through the Database Diagrams feature in
the SQL 2K. Using the Server Explorer, i can see the Database Diagrams, but
when i try to "drop" the Database Diagrams into the page, it gives the error
message.

I would like to know the procedures to retrieve the database relationships
from Database Diagrams and manipulate them through ADO.NET

I prefer to "convert" already defined relationship using SQL Server Database
Diagrams into XSD file or probably there is another method to "read" those
relationship and manipulate them.

Thank you very much for all your help

View 3 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

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

Cannot Create Linked Server

Jul 3, 2007

I am trying to create a linked server in Management Studio Exoress. In the Objext Explorer, I open Server Objexts and the right-click on Linked Servers and select New Linked Server. I then get an error that says "Cannot show the requested dialog. Additional information: Cannot find table 0. (System Data). The full text of the error is as follows:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

------------------------------
Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()



I am running XP SP2 and SSE SP2. One other item is that the providers folder is empty. I checked another box and there are several providers listed in that installation. It looks like when the SSE is installed, the providers are not being created. I have tried uninstalling and reinstalling and am having the same problem. Is this a installation bug or is there a conflict with another program? I also re-downloaded the installation files in case there was a problem with that, but it didn't solve the issue either.







Thanks for any help,



Paul Nelson

View 7 Replies View Related

Trying To Create A Relationship

Aug 15, 2005

Here is the error I am getting'role' table saved successfully'users' table- Unable to create relationship 'FK_users_role'.  ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_users_role'. The conflict occurred in database 'raintranet', table 'role', column 'role_id'.table rolerole_id intname varchar 50table usersusers_id introle_id inTrying to get table.role_id to be related to role.role_idAny help would be appreciated

View 1 Replies View Related

How To Create A One-to-one Relationship

Mar 26, 2006

hello all,
I am new to SQL server and dont have a clue on how to create a one-to-one relationship
in sql server2000. Say, I have a table 'A' with a PK <customer_id> and another table 'B' with a PK <order_id>. Now to define a one-to-one relation between them how to do that?
thanks in advance.

-tanveer

View 3 Replies View Related

Best Way To Create The Relationship

Mar 28, 2008

Hi - SQL beginer here....

SQL2005

I have a table dbo.server

Compid - 1
Name PK - Server1
Make - HP
etc...

I have just created a new table dbo.ProcessorInfo with the following columns:

Name
BrandName
ProcessorCoreCount
etc....

This table will have more than 1 record for each name:

Server1 Intel 2
Server1 Intel 2
Server2 Intel 1
Server2 Intel 1

and so on.

What is the best way to relate the name in dbo.server and name in dbo.ProcessorInfo so this is a one to many? Obviously I can't set the name column as a PK as I have more than 1 record of the same value in it.

Help much appreciated!

View 3 Replies View Related

How To Create A Relationship?

Sep 18, 2006

How do I create a relationship between two tables when the two columns that should connect each table have different names?

i.e. Table Person Column Name < -- > Table Employee Column Person Name

The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.



View 3 Replies View Related

Create (U)SP In Database On Linked Server

Aug 1, 2006

Hi,
I'm using a couple of linked servers.
I want to create a stored procedure on all of the linked servers in a database with a name which exists on all of the linked servers.
For executing SQL on all of the linked servers I'm using:

declare @x int
declare @dbname varchar(500)
declare @SQL nvarchar(600)
set @x = 1
create table #databases (ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances
while @x <= (select max(id) from #databases)
begin
select @dbname = name from #databases where id = @x
select @SQL='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]'
execute @SQL
set @x = @x + 1
end
drop table #databases

Is it possible to use a ‘create procedure’ in this construction?
Can anybody give me some help how to create a proper syntax for it?

Any help is kindly appreciated!

View 1 Replies View Related

Create A Linked Server To MPP File

Apr 13, 2007

I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.

But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server

select * from linktompp.testmpp.dbo.tasks

such that linktompp is the linked server name
testmpp is the catalog name
tasks is the table name i need to select data from

it shows an error that says

"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."

"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"
could anyone tell me the reason of this error

thanks




Mostafa Salama
M.Sc. in Computer Science

View 1 Replies View Related

Steps To Create A Linked Server

May 7, 2007

hi,

what are the steps to create a linked server, I have tried unsuccesfully:
The scenario is the following:
From server A I want to be able to execute a select statement to a table in server B like
select * from [server B].myRemoteDb.dbo.myremTable

I am administrator of server A but not of server B. I tried the

sp_addlinkedserver syntax but have not been succesful, so I would be grateful if somebody could just list the steps necessary to link Server B from Server A,

thank you

View 4 Replies View Related

Create Linked Exchange Server

Aug 29, 2007

I'm using sql server 2005 and exchange 2003. End result is I want to upload email attachments to a table in sql. No one seems to have any idea how do do this except with .net, which i know nothing about so not really an option for me. I found this bit of info with some extensive web searching. Can anyone help fill in the blanks? I can't get the linked server to work yet.


EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\.ackofficestoragemailservernamepublic folders'

Error received:The OLE DB provider "exoledb.DataSource.1" has not been registered.

View 1 Replies View Related

Error When Trying To Create A Linked Server

Jul 24, 2007

Hi all,

I have a problem when i try to create a linked server to a MsAccess Db. That's what i do:



--- linked server drop (in case it already exist)

sp_dropserver 'XXXXX', 'droplogins'



--- linked server creation

sp_addlinkedserver XXXXX, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0', ' serverfolderdb1.mdb'


--- login creation

sp_addlinkedsrvlogin XXXXX, FALSE, NULL, Admin, NULL


--- fill tables

sp_tables_ex XXXXX



File Db1.mdb is located on a partition (D) of the server where Sql is installed.



On login creation i get the following error :

Error -2147217900 Error during decryption. (15466) Source: Microsoft OLE DB Provider for SQL Server.

(This is not the real error message, it has been translated)



Some ideas?

Thanks




View 4 Replies View Related

Create Linked Server To SYBASE ASA 9.0

Apr 2, 2008


Hi all,

I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:


ODBC Tab


Data Source Name: dbNOM

Login Tab


Supply userID and Password: (selected)

User ID: DBA

Password: SQL

Database Tab


Server Name: dbNOM

StartLine: C:Program FilesSybaseSQL Anywhere 9win32dbeng9.exe

Database Name: (blank)

Database File: c:Project FolderDataBase.db
As I said, when i go for "test connection", works fine.

3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:


General


Linked Server: lkDbNom

Server Type: Other Data Source

Provider: Sybase Adaptive Server Anywhere Provider 9.0

Product Name: Sybase

Product Name: dbNom (the dsn name, right?)

Security


Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN)
All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:


SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')

And i get the following message:


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'SYBASE'.

Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476


So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.

The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server.
3) Created a connection in visual studio 2008 (server explorer - data connections), i can retrieve sdata...but of course the provider is .NET Framework Data Provider for ODBC and i cannot use the same connection string.

Any ideas?

P.S.

View 7 Replies View Related

Create Table With Many-to-many Relationship...

Dec 5, 2006

Hi, I come back again.
Can anyone help me to create table with many-to-many relationship. Here is my three tables
tbl_Networks
(
NID int identity(1,1) primary key,
NetworkName nvarchar(256)
)

tbl_Categories
(
CID int identity(1,1) primary key,
CateName nvarchar(256),
NID int
)

tbl_Sim
(
SID int identity(1,1) primary key,
NID int,
CID int,
NameOfSim nvarchar(256)
)
My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them


So I need some help...

View 5 Replies View Related

Unable To Create Relationship FK

Nov 17, 2007

Hi.
I get this error when i try to create a relationship in a db diagram (sql 2005)
"'tblActivedir' table saved successfully
'tblClient' table
- Unable to create relationship 'FK_tblClient_tblActivedir1'.
Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."


What i have is 2 tables.
1 named client
1 named activedir

In the client table the columns i want to bind with activedirtable are FR1 and DC1
I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir.
Fr1 has an fk relationship with activedir (pk is activedir' id)
and DC1 exactly the same in another fk.
So i want both columns to comunicate with activedir.
If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1
I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both.
Any help?
Thanks.

View 3 Replies View Related

How Do I Create A Relationship In DB Diagrammer ?

Nov 18, 2006

Hi Folks,

When I try to create a Relationship between two Tables I find the Relationship is always created from the Table I start with to itself.

I seem to have to select a Table to enable the Create Relationship option, and then when I 'Add' it creates a recursive Relationship to that Table.

I guess it must be possible to do what I am trying to do ?

I'd appreciate any advice.

Thanks.

Barry





View 3 Replies View Related

Create Relationship Programmatically

Jun 6, 2007

Hi,
I have metadata that stored my table structure and relationship. I would like to know is it possible to create table relationship programatically? Any sample?

Thank you

View 1 Replies View Related

Transact SQL :: How To Create One To Many Relationship

Jul 29, 2015

I have got a table as follow:

CREATE TABLE [dbo].[NozzleAllTbl] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[nBDId] INT NULL,
[nShellId] INT NULL,
[nTDId] INT NULL,
CONSTRAINT [PK_dbo.NozzleAllTbl] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nBDId] FOREIGN KEY ([nBDId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nShellId] FOREIGN KEY ([nShellId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nTDId] FOREIGN KEY ([nTDId]) REFERENCES [dbo].[NozzleTbl] ([Id])
);

NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:

NozzleTbl_1 = NozzleAllTbl.NozzleTbls[0]
NozzleTbl_2 = NozzleAllTbl.NozzleTbls[1]
...
NozzleTbl1_1 = NozzleAllTbl.NozzleTbls1[0]
NozzleTbl2_1 = NozzleAllTbl.NozzleTbls2[0]
....

View 2 Replies View Related

Create Trigger For Relationship

Mar 19, 2008

Hi,
I need help in creating a trigger before delete. The trigger should be in such a way that it should display a message, if there is any relationship with other table.

For example I have a table with employee details which have empid as primary key. I have another table with employee salary details where empid is foreign key. The trigger should check the relationship with these two tables. If I try to delete an emploeyee from employee details table and if there is a relationship of that employee with the salary table then the trigger should print a message. If there is no relationship then the trigger should perform the deletion.
I want to create a trigger like this.

Please help!!!!!!!!!!!!!!!!

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved