linking two datasets: time

Showing posts with label time. Show all posts

Monday, March 26, 2012

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key
. Is there any way around this?
Thanks!
You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:

>I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Ke
y. Is there any way around this?
>Thanks!

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key. Is there any way around this?
Thanks!You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:
>I have linked to some SQL Views in my Access FrontEnd. The first time I link to the Views it asks for the Primary Key and I select one. If I refresh the links using the Linked Table Wizard in Access, they seem to loose what I have set as the Primary Key. Is there any way around this?
>Thanks!

Linked Views

I have linked to some SQL Views in my Access FrontEnd. The first time I lin
k to the Views it asks for the Primary Key and I select one. If I refresh t
he links using the Linked Table Wizard in Access, they seem to loose what I
have set as the Primary Key
. Is there any way around this?
Thanks!You have two options:
--Don't use the wizard. Link in code (you'd programmatically delete
the old links and create new ones).
--If you want to use the wizard, delete the old linked views first,
and relink them from scratch.
--Mary
On Fri, 18 Jun 2004 13:24:02 -0700, Deb
<Deb@.discussions.microsoft.com> wrote:

>I have linked to some SQL Views in my Access FrontEnd. The first time I link to th
e Views it asks for the Primary Key and I select one. If I refresh the links using
the Linked Table Wizard in Access, they seem to loose what I have set as the Primary
Ke
y. Is there any way around this?
>Thanks!

Friday, March 23, 2012

linked table 'HOST' field, Access->SQLserver

when you create a table link from Access to SQL server, the connection
string identifies the host as the computer at the time the link was
made, regardless of which computer is running Access. Can these table
links be updated at run time so that the SQL Enterprise Manager will see
the login as originating from the computer of origin instead of from the
computer that made the link?
Thanks,
MarkIf you are talking about linking SQL Server tables to an Access .mdb
front end, then the best way to do that is to write VBA/DAO code that
creates the links at runtime when the application starts up, and then
when the application shuts down, code runs that deletes all the links.
For good measure, the code that runs when the application starts up
should also delete any existing links. That is the only way to
reliably clear out any security information which may have been cached
locally in the TableDef objects in the mdb.
--Mary
On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:

>when you create a table link from Access to SQL server, the connection
>string identifies the host as the computer at the time the link was
>made, regardless of which computer is running Access. Can these table
>links be updated at run time so that the SQL Enterprise Manager will see
>the login as originating from the computer of origin instead of from the
>computer that made the link?
>Thanks,
>Mark|||Now that's clever, didn't know you could do that!. I don't program in Access
,
but like the approach you suggest. Care to offer a code fragment for
create/delete example? I never liked the form/data object model in Access,
and having forms linked to missing DAO objects seems, well, interesting!
thanks,
mark
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> If you are talking about linking SQL Server tables to an Access .mdb
> front end, then the best way to do that is to write VBA/DAO code that
> creates the links at runtime when the application starts up, and then
> when the application shuts down, code runs that deletes all the links.
> For good measure, the code that runs when the application starts up
> should also delete any existing links. That is the only way to
> reliably clear out any security information which may have been cached
> locally in the TableDef objects in the mdb.
> --Mary
> On Mon, 22 Aug 2005 10:14:53 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>|||Here ya go:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
--Mary
On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
<m/g/r/o/s/s/@.deq.state.id.us> wrote:
[vbcol=seagreen]
>Now that's clever, didn't know you could do that!. I don't program in Acces
s,
>but like the approach you suggest. Care to offer a code fragment for
>create/delete example? I never liked the form/data object model in Access,
>and having forms linked to missing DAO objects seems, well, interesting!
>thanks,
>mark
>"Mary Chipman [MSFT]" wrote:
>|||Thank you; that works rather nicely.
mark/
"Mary Chipman [MSFT]" wrote:
[vbcol=seagreen]
> Here ya go:
> Public Sub LinkODBConnectionString()
> Dim strConnection As String
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Set db = CurrentDb
> ' Specify the driver, the server, and the connection
> strConnection = "ODBC;Driver={SQL Server};" & _
> " Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
> ' Specifying a SQLS user/password instead of integrated security
> ' strConnection = "ODBC;Driver={SQL Server};" & _
> ' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=p@.ss!word"
> ' Create Linked Table. The LinkedTableName and the
> ' ServerTableName can be the same.
> Set tdf = db.CreateTableDef("LinkedTableName")
> tdf.Connect = strConnection
> tdf.SourceTableName = "ServerTableName"
> db.TableDefs.Append tdf
> Set tdf = Nothing
> End Sub
> --Mary
> On Mon, 22 Aug 2005 15:56:16 -0600, Mark Gross
> <m/g/r/o/s/s/@.deq.state.id.us> wrote:
>

Wednesday, March 21, 2012

servers Dialog Error

I freshly installed SQL Server 2005 Developers Edition (with SP1) on Windows XP (with SP2) but every time I right click on “Linked Servers” and choose “New Linked Server…” I get the following error:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot find table 0. (System.Data)

BUTTONS:

Details:

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

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 tried reinstalling SQL Server 2005 and also tried Service Pack 2 (CTP) but I still get the same error.

PS I know I can use “sp_addlinkedserver” but I still need to know why the “Linked servers Dialog Box” is not showing up.

Here’s my @.@.VERSION

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thank you,

We've recently released SQL Server 2005 Service Pack 2 (RTM). If you could install that, then let us know if you're still having this issue. I know we had worked on the Linked Server dialog in SP2, but I cannot recall the scope of the work.

SQL Server 2005 Service Pack 2:
http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

It works now. I had a feeling it had something to do with “Host Integration Server 2006” so I uninstalled it, rebooted my PC and now “Linked Servers” dialog box in “SQL Server 2005 Developer Edition” works. I have no idea why it does not work with “Host Integration Server 2006” installed. Hopefully you guys have time to look into it and fix it. Also I’m very curious why this happens.

Thank you,

|||

I have this error too.

My @.@.version is

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) (i.e I have SP2)

I'm having trouble setting uo a linked server (SQL 2000) to my brand new SQL 2005. When I try to access the Linked Server (SQL2000) from the 2005 Server I get the following error message:

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "zerver" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "zerver". The provider supports the interface, but returns a failure code when it is used.

I used sp_addlinkedserver och sp_addlinkedsrvlogin to set up the connection between the servers.

SQL 2000 version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thank you,

Johan Nilsson

|||

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server

http://support.microsoft.com/kb/906954/en-us

SYMPTOMS
Consider the following scenario. You define a linked 32-bit Microsoft SQL Server 2000 server or a linked SQL Server 7.0 server by using the sp_addlinkedserver stored procedure. Then, you try to run distributed queries from a 64-bit SQL Server 2005 client to the linked server. In this scenario, you may experience one of the following symptoms: ? If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message:
The ODBC catalog stored procedures installed on server <LinkedServerName> are version <OldVersionNumber>; version <NewVersionNumber> or later is required to ensure proper operation. Please contact your system administrator.
? You receive an error message if the following conditions are true: ? SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server.
? The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.
The error message is similar to the following:
The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used.
Note If you use the linked SQL Server 2000 server and the server has not been upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4, you must install SQL Server 2000 SP3 or SQL Server 2000 SP4 first.

sql