accessing from T-SQL a database on another DBMS

tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what you want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-think the approach if
possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>> tHi
>> I want to build a trigger to modify data in a table in a database that is
>> running on another Database Engine in may LAN. I don' t know if that is
>> possible. If it is, how will I make the connection to that database?
>> Thanks in advance - Waldo
>

accessing from T-SQL a database on another DBMS

tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - Waldo
I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>

accessing from T-SQL a database on another DBMS

tHi
I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?
Thanks in advance - WaldoYou would have to set up the other instance as a "linked server". But what y
ou want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-t
hink the approach if
possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
> tHi
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
> Thanks in advance - Waldo|||I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.
You can get DTC to do what you need however, but it is often a PITA.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@.TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "wvandenbroeck" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>

Access read only variables in Script Component in a Dataflow

I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.Use "Me.Variables.variableName"|||I was I am trying to do the same thing you mentioned but I am the following error

[Script Component [1463]] Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables() at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.Variables.get_LogPath() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ComponentWrapper:line 72 at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.ScriptMain..ctor() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ScriptMain:line 78 End of inner exception stack trace at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()|||Script Component or Script Task?|||Script Component
|||

Rohit Ghule wrote:

Script Component

Does your script component have all of the Imports?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in via ReadOnlyVariables

Row.MaxKey = MaximumKey 'Assign the output field of "MaxKey" to the value of the passed in variable
End Sub

End Class|||Searching this forum turned up more information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201158&SiteID=1

I think the key is to look at your variables in the Public Overrides Sub section.|||

Most methods in a script task are overrides of base class methods, so you need a bit more info. The PreExecute, which is public, and overridden, so starts with "Public Overrides PreExecute(.." supports the variable manager stuff, but may not always be what you want. If you really need to access variable at a row level then you can, see this thread-

Re: R/W access problem with var in script Component - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1)

Access front-end - concurrency

When I build an MS Access front-end for an SQL Server backend, how does it take care of data integrity and concurrency , if it is only a front-end ?
Is Access smart enough to do the job ?
Thanks.Data integrity should be handled by SQL Server, using constraints, foreign keys, triggers, etc.
Concurrency should not be an issue as long as you keep the number of simultaneous users low. Access forms operate by downloading an entire recordset and allowing the user to step through them. This is not the optimal implementation for an Enterprise Application. You can get around this by writing your forms to retrieve one record at a time, but then you sacrifice these simple coding that is one of the big benefits of using Access.