Create Relationship With Tables In A Linked Server
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 Complete Forum Thread with Replies
Related Forum Messages:
How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?
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 Replies !
How T Create Relationship B/w Two Tables
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 Replies !
How To Create A Datagrid For Two No Relationship Tables
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 Replies !
Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
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 Replies !
Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
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 Replies !
How To Use The Create Relationship Function At MS SQL Server
hi all, For those who will celebrate the chinese new year, Gong Xi Fa Cai!!! I now using microsoft SQL server to manage my database. To manage the database, I have go through the SQL Server Enterprise Manager To create my database. Now I am the stage create the relationship for my relations. So when I drag the foreign key from one relation to primary key at another relation, vice versa, it will pop up the 'Create Relationship' form. What I can saw is there are three check boxes. one check boxes have 2 sub check boxes. Quote: Checkbox 1 - [Check existing data on creation] Checkbox 2 - [Enforce relationship for replication] Checkbox 3 - [Enforce relationship for INSERTs and UPDATEs] Checkbox 3.1 - [Cascade Update Related Fields] Checkbox 3.2 - [Cascade Delete Related Records] Usually the Checkbox 1, 2, 3 had been checked. but the check box 3.1 and 3.2 is display as uncheck by default. Another question is when I linked up the relationship between two of the relations will appear a asterisk(*) beside the relation's name. Why? But I not very understand to the check boxes means and the asterisk. Can someone give me some guidelines!!Thanks Thanks in billions.... Best regards John Ang
View Replies !
Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource
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 Replies !
Failing To Make Relationship Between To Tables Of SQL Server DB
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 Replies !
Failing To Create Relationship (SQL Server Claims Table Lacks PK For Some Reason ?)
I tried to create a relationship in EM's diagram pane inSQL Server 2000 (I'd list the version of EM, but About gives methe MMC version, which is probably not relevant.)(The database itself is SQL Server 2000 SP3.)I got an error that I don't understand (because, at leastat first blush, it appears to be quite untrue). Note thatI have never clicked before on the diagrams child of thedatabase; this was entirely experimental."Primary key or UNIQUE constraint must be defined fortable 'xxx' before it can participate in a relationship."http://msdn.microsoft.com/library/d...cantbepktbl.asp1) My table already had a primary key; why is MS SQL Server apparentlyclaiming otherwise ? (I don't think I can define a second primary keyon the same table. I could perhaps define an additional unique indexon top of the primary key, but, I'm not sure.)2) What does it mean: table '<0s>' ? That is, what does 0s mean ?
View Replies !
Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager
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 Replies !
Retrieving The Tables Relationship From SQL Server Database Diagrams
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 Replies !
Trying To Create A Relationship
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 Replies !
How To Create A Relationship?
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 Replies !
Best Way To Create The Relationship
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 Replies !
How To Create A One-to-one Relationship
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 Replies !
Cannot Create Linked Server
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 Replies !
Create Trigger For Relationship
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 Replies !
How Do I Create A Relationship In DB Diagrammer ?
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 Replies !
Create Table With Many-to-many Relationship...
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 Replies !
Unable To Create Relationship FK
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 Replies !
Create (U)SP In Database On Linked Server
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 Replies !
Create Linked Server To SYBASE ASA 9.0
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 Replies !
Error When Trying To Create A Linked Server
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 Replies !
Create Linked Exchange Server
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 Replies !
Steps To Create A Linked Server
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 Replies !
Create A Linked Server To MPP File
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 Replies !
Create Table With Recursive Relationship
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View Replies !
Create A View For One-to-many Relationship Table
hi.. I would like to create a view for two tables which have a one-to-many relationship. Code: Table: Supplier Supp_ID 1 Table:Supplier_category Supp_ID,StockCategoryID 1,56 1,57 1,90 How can i create a view that has columns like below: Supp_ID, Stock 1,[56,57,90] Thanks in advance.
View Replies !
Stored Procedue 2 Create A Relationship.
i have a table where the feilds are 1.fromtable 2.fromfeild 3.fromcategory 4.totable 5.tofeild 6.tocategory. i have write a stored proceduer for creating 2 relationship between fromfeild & tofeild from the same value in from category & in tocategory. there r around 465 records in a table. so anyone can comeout for solution of this. hope soon i get a solution for this.
View Replies !
Create Or Show Relationship Digram
Dear sir or madam I have a problem related to create or show relationship digram in sql server 2005. Especially, I want to show relationship diagram that I established in sqlserver 2000 in sql server 2005 but I can't and I don't how to do it. I look forward to hearing. Thank you in advance! Best regard, seyha moth
View Replies !
How To Create A Linked Server To MySQL From MsSQL?
I can create a linked server to another MsSQL from MsSQL,but encounter error when create a linked server to MySQL: Error 7399:OLE DB provider 'MSDASQL' reported an error. Data source name not found and no default driver specified. ...... Anyone can help me? Thanks!
View Replies !
How Can I Create A Temp Table On A Linked Server?
Hello I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so: create table #myRemoteTempTable insert into #myRemoteTempTable select * from myLocalTable update myRemoteTable set Value=#myRemoteTempTable.Value from myRemoteTable inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID
View Replies !
HELP - Error 15028 - Can't Create Linked Server
I have 2 instances of server A. A is the primary instance and A1 is the logical instance. Instance A1 has a linked server to server B. When I try to create a linked server from the primary instance A to server B, I get the error message Error 15028: The server 'B' already exists. When I run a select of master..sysservers on the primary instance, A, I see all linked servers for A and A1. When I select off of A1 I only see the instances for A1. If I try to access server B from the primary instance A, I receive the following message: Server: Msg 7411, Level 16, State 1, Line 1 Server 'B' is not configured for DATA ACCESS. (1) Why does sysservers on the primary instance, A, show instances for A and A1 and not just the ones for A? (2) Why can't I create the linked server 'B' on instance 'A'? Thanks, Dave
View Replies !
Create Table In MS-Access From SQL Server (linked)
Hi, The scenario is I have linked server with access. I want to check the MS-Access Database table weather the table Exists or Not. If Exists I have insert the newly updated records. else I have to create a same table and I have to insert the records. Now my question is how could I check weather the table is exists or not in MS-Access Database and If Not Exists, Then How can I create the new table thru linked server Query Thanks --Krishna
View Replies !
Relationship Of Tables Pls
:rolleyes: can u send me deails for establish relationship of tables i know namw of then but i need them by examples ans defination as 1 simple 2 complex 3 muliple u can send me ink if u get fron google also thanks bye
View Replies !
Create Temp Table On Linked Server From Local Server
Hi, I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large. If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that. I appreciate any advice / guidance anyone can offer me!
View Replies !
Can You Have Linked SQL Server Tables?
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 Replies !
Tables In Linked Server
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 Replies !
Some Tables Do Not Appear When Using Linked Server
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 Replies !
How Can I Update Relationship Tables?
<----------I have 2 tables are: 'customers (parent)' and 'open_ac (child)' <--------I have tried to insert and update data into sql database by using textboxes (don't use datagrid) <--------My tables details are below <-------this table uses for keeping user data customers fields: Column name type length Description cu_id int 4 Primary key Identifiers cu_fname nvarchar 20 allow null first name cu_lname nvarchar 40 allow null last name cu_nat nvarchar 20 allow null nationality cu_add nvarchar 40 allow null address cu_wplace nvarchar 40 allow null workplace cu_tel nvarchar 10 allow null telephone cu_fax nvarchar 10 allow null fax cu_email nvarchar 10 allow null email <----the open_ac uses for keeping register date/time of customers open_ac fields: Column name type length Description cu_id int 4 link key op_date date/time 8 register date <----------my code Imports System.Data.SqlClient Public Class cus_reg Inherits System.Web.UI.Page Dim DS As DataSet Dim iRec As Integer 'Current Record Dim m_Error As String = "" Public Property MyError() As String Get Return m_Error End Get Set(ByVal Value As String) m_Error = Value If Trim(Value) = "" Then Label3.Visible = False Else Label3.Text = Value Label3.Visible = True End If End Set End Property Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then Dim C1 As New MISSQL 'DS = C1.GetDataset("select * from customers;select * from open_ac;select * from accounts") DS = C1.GetDataset("select * from customers;select * from open_ac") Session("data") = DS iRec = 0 Viewstate("iRec") = iRec Me.MyDataBind() Dim Dtr As DataRow = DS.Tables(0).NewRow DS.Tables(0).Rows.Add(Dtr) iRec = DS.Tables(0).Rows.Count - 1 viewstate("iRec") = iRec Me.Label2.Text = DateTime.Now Me.MyDataBind() Else DS = Session("data") iRec = ViewState("iRec") End If Me.MyError = "" End Sub Public Function BindField(ByVal FieldName As String) As String Dim DT As DataTable = DS.Tables(0) Return DT.Rows(iRec)(FieldName) & "" End Function Public Sub MyDataBind() Label1.Text = "Record : " & iRec + 1 & " of " & DS.Tables(0).Rows.Count txtcu_id.DataBind() txtcu_fname.DataBind() txtcu_lname.DataBind() txtcu_add.DataBind() txtcu_occ.DataBind() txtcu_wplace.DataBind() txtcu_nat.DataBind() txtcu_tel.DataBind() txtcu_fax.DataBind() txtcu_email.DataBind() End Sub Here is update code Private Sub bUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bUpdate.Click Dim DT As DataTable = DS.Tables(0) Dim DR As DataRow = DT.Rows(iRec) 'Dim DR1 As DataRow = DT1.Rows(iRec) If DR.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then Me.MyError = "please enter your id" Exit Sub End If DR("cu_id") = txtcu_id.Text End If If txtcu_fname.Text.Trim = "" Then Me.MyError = "please enter your name" Exit Sub Else DR("cu_fname") = txtcu_fname.Text End If If txtcu_lname.Text.Trim = "" Then Me.MyError = "please enter your last name" Exit Sub Else DR("cu_lname") = txtcu_lname.Text End If If txtcu_add.Text.Trim = "" Then Me.MyError = "please enter your address" Exit Sub Else DR("cu_add") = txtcu_add.Text End If If txtcu_occ.Text.Trim = "" Then Me.MyError = "please enter your occupation" Exit Sub Else DR("cu_occ") = txtcu_occ.Text End If If txtcu_wplace.Text.Trim = "" Then Me.MyError = "please enter your workplace" Exit Sub Else DR("cu_wplace") = txtcu_wplace.Text End If If txtcu_nat.Text.Trim = "" Then Me.MyError = "Please enter your nationality" Exit Sub Else DR("cu_nat") = txtcu_nat.Text End If If txtcu_tel.Text.Trim = "" Then DR("cu_tel") = DBNull.Value Else DR("cu_tel") = txtcu_tel.Text End If If txtcu_tel.Text.Trim = "" Then DR("cu_fax") = DBNull.Value Else DR("cu_fax") = txtcu_fax.Text End If If txtcu_email.Text.Trim = "" Then DR("cu_email") = DBNull.Value Else DR("cu_email") = txtcu_email.Text End If Dim Strsql As String If DR.RowState = DataRowState.Added Then Strsql = "insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)" Else Strsql = "update customers set cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10 where cu_id =@P1" End If Dim C1 As New MISSQL Dim cmd As SqlCommand = C1.CreateCommand(Strsql) C1.CreateParam(cmd, "ITTTTTTTTT") cmd.Parameters("@P1").Value = DR("cu_id") cmd.Parameters("@P2").Value = DR("cu_fname") cmd.Parameters("@P3").Value = DR("cu_lname") cmd.Parameters("@P4").Value = DR("cu_add") cmd.Parameters("@P5").Value = DR("cu_occ") cmd.Parameters("@P6").Value = DR("cu_wplace") cmd.Parameters("@P7").Value = DR("cu_nat") cmd.Parameters("@P8").Value = DR("cu_tel") cmd.Parameters("@P9").Value = DR("cu_fax") cmd.Parameters("@P10").Value = DR("cu_email") Dim Y As Integer = C1.Execute(cmd) If Y > 0 Then DR.AcceptChanges() Else Me.MyError = "Can not register" End If <---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update Dim DT1 As DataTable = DS.Tables(1) Dim DR1 As DataRow = DT1.Rows(iRec) If DR1.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then Me.MyError = "Please enter id" Exit Sub End If DR1("cu_id") = txtcu_id.Text End If If Label2.Text.Trim = "" Then DR1("op_date") = Label2.Text End If Dim StrSql1 As String If DR1.RowState = DataRowState.Deleted Then StrSql1 = "insert into open_ac (cu_id,op_date) values (@P13,@P14)" Else StrSql1 = "update open_ac set op_date=@P14 where cu_id=@P13" End If Dim C2 As New MISSQL Dim cmd2 As SqlCommand = C2.CreateCommand(StrSql1) C2.CreateParam(cmd2, "ID") cmd2.Parameters("@P1").Value = DR1("cu_id") cmd2.Parameters("@P2").Value = DR1("op_date") Dim Y1 As Integer = C2.Execute(cmd2) If Y1 > 0 Then DR1.AcceptChanges() Else Me.MyError = "Can not register" End If End Sub End Class <------this is class I use for connecting to database and call parameters.... MISSQL class Imports System.Data.SqlClient Public Class MISSQL Dim PV As String = "Server=web_proj;uid=sa;pwd=sqldb;" Dim m_Database As String = "c1_itc" Public Strcon As String Public Sub New() Strcon = PV & "database=" & m_Database End Sub Public Sub New(ByVal DBName As String) m_Database = DBName Strcon = PV & "database=" & m_Database End Sub Public Property Database() As String Get Return m_Database End Get Set(ByVal Value As String) m_Database = Value Strcon = PV & "database=" & m_Database End Set End Property Public Function GetDataset(ByVal Strsql As String, _ Optional ByVal DatasetName As String = "Dataset1", _ Optional ByVal TableName As String = "Table") As DataSet Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DS As New DataSet(DatasetName) Try DA.Fill(DS, TableName) Catch x1 As Exception Err.Raise(60002, , x1.Message) End Try Return DS End Function Public Function GetDataTable(ByVal Strsql As String, _ Optional ByVal TableName As String = "Table") As DataTable Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DT As New DataTable(TableName) Try DA.Fill(DT) Catch x1 As Exception Err.Raise(60002, , x1.Message) End Try Return DT End Function Public Function CreateCommand(ByVal Strsql As String) As SqlCommand Dim cmd As New SqlCommand(Strsql) Return cmd End Function Public Function Execute(ByVal Strsql As String) As Integer Dim cmd As New SqlCommand(Strsql) Dim X As Integer = Me.Execute(cmd) Return X End Function Public Function Execute(ByRef Cmd As SqlCommand) As Integer Dim Cn As New SqlConnection(Strcon) Cmd.Connection = Cn Dim X As Integer Try Cn.Open() X = Cmd.ExecuteNonQuery() Catch X = -1 Finally Cn.Close() End Try Return X End Function Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal StrType As String) 'T:Text, M:Memo, Y:Currency, D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture Dim i As Integer Dim j As String For i = 1 To Len(StrType) j = UCase(Mid(StrType, i, 1)) Dim P1 As New SqlParameter P1.ParameterName = "@P" & i Select Case j Case "T" P1.SqlDbType = SqlDbType.NVarChar Case "M" P1.SqlDbType = SqlDbType.Text Case "Y" P1.SqlDbType = SqlDbType.Money Case "D" P1.SqlDbType = SqlDbType.DateTime Case "I" P1.SqlDbType = SqlDbType.Int Case "S" P1.SqlDbType = SqlDbType.Decimal Case "B" P1.SqlDbType = SqlDbType.Bit Case "P" P1.SqlDbType = SqlDbType.Image End Select Cmd.Parameters.Add(P1) Next End Sub End Class <-------Thank you in advance<-------and Thank you very much for all help
View Replies !
Establish Relationship Between 2 Tables
I am running SQL Server CE on Visual Studio 2005. I have created 2 tables and wish to establish a relationship between the two tables. However, I could not find a proper way to establish the connection. Anyone can provide some help on this? thank you.
View Replies !
Combing Two Tables With No Relationship
Which way to go? Have two separate programs(Visual Studio-Coded in Visual Basic) that share the same sql 2005 database(called Night Audit) The tables are called RoomSettlement and Settlement. They were separate tables and separate programs in the past because it was just exported in excel to a visual integrator that dumped the amounts into our accounting software. Now needing to combine the two tables(that have no link) on to a single excel sheet daily where the tables have the same date: Table: Room Settlement – Field: RoomSettlementDatetxt = Table: Settlement – Field: SettlementDatetxt Any Ideas on the best way to go? (Link the tables with a foreign key and primary key, maybe union based on date, or any easier suggestion with out having to change a lot of the program ) Room Settlement Table PrimaryKey Availabilitytxt Ratetxt Othertxt Arrivalstxt departurestxt GuestCounttxt HotelOccupancytxt AverageDailyRatetxt RevPahrtxt HotelRevenuetxt ownerrentaltxt hoteltransienttxt hotelcorptxt hotelgovtxt hotelpacktxt hotelexpediatxt hotelsynxistxt hotelothertxt rentalpooltxt employeetxt Hotelnightstxt activitestxt Bonustxt Golftxt Tvinternettxt laundrytxt misctxt petstaytxt Telephonetxt Purchaseunittxt Damageincometxt resalerevtxt latefeestxt IntrestIncometxt Taxestxt RoomSettlementDatetxt sitetxt senddatetxt Settlement Table: PrimaryKeyintUn PreviousGuesttxt TransferCityLedgertxt ActivityGuesttxt transferfromadvdepositguesttxt transfromguesttoguesttxt GuestchagefromInctxt Guestnetcashtxt Guestcheckstxt guestpaidout guestlockboxtxt guestaetxt GuestDinerstxt GuestDiscovertxt GuestMastercardtxt GuestVisatxt guestdepositchangecashtxt guestnetchangetxt guestnewbalancetxt guestsystembalancetxt guestvariance PreviousCity ActivityCitytxt Citynetcash Citychecks citypaidouttxt citylockboxtxt cityaetxt CityDinerstxt CityDiscovertxt CityMastercardtxt CityVisatxt citydepositchangecashtxt citynetchangetxt citynewbalancetxt citysystembalancetxt cityvariance Sitetxt DateStamptxt SettlementDatetxt Thanks JK
View Replies !
|
TRACKER
