active-x replication controls and slow link

Hi
Have a client who has a slow dialup connection. Synchronisation keeps
failing because of this.
I''m using the active-x controls. Are there any parameters/properties I can
set to tell it to hang on and be patient, and generally be more reliable ?
Thanks
Bruce
Bruce,
try increasing the logintimeout and querytimeout properties.
HTH,
Paul Ibison
sql

Active Directory/ADSI connection problems...Here's the correct syntax

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

|||

Hi,

Not sure if this is the correct forum but I also need some help on a similar matter. I have connected my Sharpoint Server to ADAM and imported the users and groups into Sharepoint, however I want to display some SQL 2005 data in a web part, how can I get the ADAM user to be recognized by SQL 2005 using the current logged in Sharepoint (ADAM) User.

Jos

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks

active directory connection

Hi,
can I connect sql server 2000 to active directory, even
thay are on different machine but on the same domain ?
How ?
Thanks a lot!!!Check out :
http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt for
an example using Linked server and LDAP query from SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"don pasquale" <anonymous@.discussions.microsoft.com> wrote in message
news:06c201c39314$e6bcaea0$a001280a@.phx.gbl...
> Hi,
> can I connect sql server 2000 to active directory, even
> thay are on different machine but on the same domain ?
> How ?
> Thanks a lot!!!
>

Active Directory access in SSIS

Does anyone have any examples of setting up a connection manager in SSIS to read information from Microsoft Active Directory? I would like to retrieve a list of users and populate them into a SQL Server table.

Thanks!
Harry

BOL has an example for a script task but it should be fairly straight forward to convert it to a script component. There is also an OLEDB driver for active directory that should be able to be used directly from the OLEDB source (although I don't know whether anyone has actually tried using it yet).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/a88fefbb-9ea2-4a86-b836-e71315bac68e.htm

HTH,

Matt

Acquiring Connection manager Programmatically in SSIS

hi all,

I m new to this forum and hope i ll get warm welcome from all of you.... thank you

I m Praveen kumar Dayanithi... a master student doin my assistantship in a Company. Kindly help me with this...

Here is my issue.... i would like to know how can i pass arguments to connection manager programmatically. In other words how can i acquire connection manger through SCRIPT task(Vb script). I know it is very easy to manually select n specify database name, table name by right clicking in dataflow task but in my company if i do that it will be very cumbersome for production people to change all the database and table names manually when the project is moved to production. Rather if i programmatically acquire connection manager using global variables it will make production people's job very easy. They have to just change the value of the variables. So can any one help me with this issue.

Thanks n regards

For dynamically changing configurations have you tried Package Configurations

Check out

http://msdn2.microsoft.com/en-us/library/ms141682.aspx
http://sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx
http://www.sqlis.com/default.aspx?26

Thanks,
Loonysan

AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?
|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

Acess 2003 connection problem to SQL Server

I have a small group who uses Access 2003 to connect to SQL Server. The
users run XP pro and Access 2003. A common DSN is created on each user’s
box. They are connecting to a box with Windows 2000 Standard Svc Pack 4 &
SQL Server 2000 Standard Svc Pack 3a. They are linking to the SQL Server
tables. We are using windows authentication.
The two users are not able to access SQL Server at the same time. The error
message they are receiving is:
Microsoft Office Access
Could not execute query; could not find liked table.
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
‘dbo.MAIN. (#208)
Interesting though is that if the user drops the e linked table and try to
relink it the objects that appear are from the Master database.
I verified the Group the users belong to have the correct default database
set.
I am at a loss and any help or hints to get me going in the right direction
would be appreciated.
It will work better for you if you ditched the DSNs and wrote VBA/DAO
code to relink the tables. You can supply ODBC connection string
information directly when the application loads, and delete the
TableDef objects when the application closes. You supply a source
table name, and the name of the link in the FE separately. This way
you pick up any schema changes to the underlying tables, which
invalidates old links. You can get the correct connection string from
http://able-consulting.com/ADO_Conn.htm. I recommend using DAO for
this instead of ADOX because there have been problems in the past with
linked tables coming in read-only.
--Mary
On Thu, 12 Aug 2004 09:21:02 -0700, "T Man"
<TMan@.discussions.microsoft.com> wrote:

>I have a small group who uses Access 2003 to connect to SQL Server. The
>users run XP pro and Access 2003. A common DSN is created on each users
>box. They are connecting to a box with Windows 2000 Standard Svc Pack 4 &
>SQL Server 2000 Standard Svc Pack 3a. They are linking to the SQL Server
>tables. We are using windows authentication.
>The two users are not able to access SQL Server at the same time. The error
>message they are receiving is:
>Microsoft Office Access
>Could not execute query; could not find liked table.
>[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>dbo.MAIN. (#208)
>
>Interesting though is that if the user drops the e linked table and try to
>relink it the objects that appear are from the Master database.
>I verified the Group the users belong to have the correct default database
>set.
>I am at a loss and any help or hints to get me going in the right direction
>would be appreciated.
|||Mary,
I am confused regarding the stance on the usage of DAO... In an email to
another user it appears you do not favor the use of DAO...
(At this point in time, DAO 3.6 is truly ancient...), however, in this case
it appears preferred.
Is it OK for linking, but not OK to perform updates ? Just want to know the
difference.
Thanks
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:s0oph0tv4tkue147148v4qccketajvdetj@.4ax.com... [vbcol=seagreen]
> It will work better for you if you ditched the DSNs and wrote VBA/DAO
> code to relink the tables. You can supply ODBC connection string
> information directly when the application loads, and delete the
> TableDef objects when the application closes. You supply a source
> table name, and the name of the link in the FE separately. This way
> you pick up any schema changes to the underlying tables, which
> invalidates old links. You can get the correct connection string from
> http://able-consulting.com/ADO_Conn.htm. I recommend using DAO for
> this instead of ADOX because there have been problems in the past with
> linked tables coming in read-only.
> --Mary
> On Thu, 12 Aug 2004 09:21:02 -0700, "T Man"
> <TMan@.discussions.microsoft.com> wrote:
&[vbcol=seagreen]
error[vbcol=seagreen]
to[vbcol=seagreen]
database[vbcol=seagreen]
direction
>

Accuracy of replicated data

Hi,
I use dial-up connection to replicate data between to servers. Our
replication type is Merge and I'm worried that what happens if the
connection fails when merge agent is working. Will the synchronization be
rolled back or I must necessarily use Transactional replication?
Thanks in advance,
Leila
"Leila" schrieb:

> Hi,
> I use dial-up connection to replicate data between to servers. Our
> replication type is Merge and I'm worried that what happens if the
> connection fails when merge agent is working. Will the synchronization be
> rolled back or I must necessarily use Transactional replication?
> Thanks in advance,
> Leila
Test it (it should be rolled back - and even if not: the next execution of
the merge agent should synchronize the publisher and subscriber again)!
If you you do not need bidirectional replication it is better to use
transactional replication anyway - it's faster and (unlike the merge
replication) guarantees the consistency of your data!
|||Thanks Christian,
But I'm trying to understand its logic without testing. Because my data are
important and sensitive and I cannot risk for testing!
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in
message news:44A7E2F0-23EC-490E-82CD-820D15257830@.microsoft.com...[vbcol=seagreen]
> "Leila" schrieb:
be
> Test it (it should be rolled back - and even if not: the next execution of
> the merge agent should synchronize the publisher and subscriber again)!
> If you you do not need bidirectional replication it is better to use
> transactional replication anyway - it's faster and (unlike the merge
> replication) guarantees the consistency of your data!

access-SQL ODBC link

> Hello,
> I am using trusted windows user connection to SQL
server.
> I created an ODBC link from Access to SQL server. I am
> able to modify data in tables through SQL Enterprise
> Manager however I am not able to modify them in Access.
> What is missing?You need to create a primary key or unique index on any SQLS tables
you want to modify from Access.
--mary
On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>server.|||Why do I have to do that? And if my primary key is based
on many columns will that cuse any problem?
>--Original Message--
>You need to create a primary key or unique index on any
SQLS tables
>you want to modify from Access.
>--mary
>On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
><anonymous@.discussions.microsoft.com> wrote:
>
Access.[vbcol=seagreen]
>.
>|||Why? because it won't work if you don't. Access can't locate the row
on the server, update it, and guarantee data integrity if the table
doesn't have a PK. If your PK is based on too many columns, then you
should consider a surrogate PK. A good starting point would be
"Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design" by Mike Hernandez.
--Mary
On Sun, 18 Apr 2004 17:58:46 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Why do I have to do that? And if my primary key is based
>on many columns will that cuse any problem?
>SQLS tables
>Access.

access-SQL ODBC link

> Hello,
> I am using trusted windows user connection to SQL
server.
> I created an ODBC link from Access to SQL server. I am
> able to modify data in tables through SQL Enterprise
> Manager however I am not able to modify them in Access.
> What is missing?
You need to create a primary key or unique index on any SQLS tables
you want to modify from Access.
--mary
On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>server.
|||Why do I have to do that? And if my primary key is based
on many columns will that cuse any problem?
>--Original Message--
>You need to create a primary key or unique index on any
SQLS tables[vbcol=seagreen]
>you want to modify from Access.
>--mary
>On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
><anonymous@.discussions.microsoft.com> wrote:
Access.
>.
>
|||Why? because it won't work if you don't. Access can't locate the row
on the server, update it, and guarantee data integrity if the table
doesn't have a PK. If your PK is based on too many columns, then you
should consider a surrogate PK. A good starting point would be
"Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design" by Mike Hernandez.
--Mary
On Sun, 18 Apr 2004 17:58:46 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Why do I have to do that? And if my primary key is based
>on many columns will that cuse any problem?
>SQLS tables
>Access.

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?

When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.

|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?
When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.

|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing SQL Server through VPN

We have a VPN set up and want to be able to work with SQL Server from home
(a minimum 4Mbit connection) we have applications we want to run that
directly interact with SQL Server and depend on SQLDMO to enumerate server
listings... I can connect to servers in enterprise manager by using their
name or IP's but it will not give me a list of servers on the network from
the VPN Client end... because of this the applications that depend on SQLDMO
also are not working because they enumerate the servers through SQLDMO, and
they return a blank list... is there any way to get SQLDMO to work so we can
get a server list back?
Our network is
Windows 2003 SP1 standard servers with ISA 2004 SP1... All Internal ports
are fowarded to the VPN with no exceptions or filters on them.
Are you authenticated fully through your VPN connection? I am curious
if this has to do with the domain your home PC may/may not be a part of.
Shahryar
Brian Henry wrote:

>We have a VPN set up and want to be able to work with SQL Server from home
>(a minimum 4Mbit connection) we have applications we want to run that
>directly interact with SQL Server and depend on SQLDMO to enumerate server
>listings... I can connect to servers in enterprise manager by using their
>name or IP's but it will not give me a list of servers on the network from
>the VPN Client end... because of this the applications that depend on SQLDMO
>also are not working because they enumerate the servers through SQLDMO, and
>they return a blank list... is there any way to get SQLDMO to work so we can
>get a server list back?
>Our network is
>Windows 2003 SP1 standard servers with ISA 2004 SP1... All Internal ports
>are fowarded to the VPN with no exceptions or filters on them.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||All we are doing is the VPN authentication... user name, domain and password
over an IPSec VPN Connection
|||Hi Brian,
I understood the scenario as:
1. You could connect to the SQL Server via Enterprise Manager or Query
Analzyer
2. But if you want to select all the SQL instances in the LAN, you will get
blank information
3. You are connecting with VPN.
If I have misunderstood your concern, please feel free to point it out.
Please download ListSQLSvr.exe and execute to see whether ListSQLSvr will
generate all the instances information in your LAN.
ListSQLSvr.exe - Enumerate SQL Servers
http://www.sqldev.net/misc/ListSQLSvr.htm
If it does get all the SQL instances information, I believe you should
check your custom SQL- DMO application.
Also, please confirm you have SQL-DMO installed on your client.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||nothing is listed when ran... enterprise manager does not even see the
servers when ran over the VPN client side computer, but they show up when
ran on the internal network...
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:SpqmWpt1FHA.3276@.TK2MSFTNGXA01.phx.gbl...
> Hi Brian,
> I understood the scenario as:
> 1. You could connect to the SQL Server via Enterprise Manager or Query
> Analzyer
> 2. But if you want to select all the SQL instances in the LAN, you will
> get
> blank information
> 3. You are connecting with VPN.
> If I have misunderstood your concern, please feel free to point it out.
> Please download ListSQLSvr.exe and execute to see whether ListSQLSvr will
> generate all the instances information in your LAN.
> ListSQLSvr.exe - Enumerate SQL Servers
> http://www.sqldev.net/misc/ListSQLSvr.htm
> If it does get all the SQL instances information, I believe you should
> check your custom SQL- DMO application.
> Also, please confirm you have SQL-DMO installed on your client.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Brian,
Thanks for the follow up.
Microsoft OLEDB Provider for SQL Server (SQLOLEDB) uses the following two
methods to enumerate all the existing servers on the particular subnet:
- Method 1: SQLOLEDB calls NetServerEnum API to enumerate all the SQL
Server instances on the particular subnet.
- Method 2: SQLOLEDB sends a UDP packet to enumerate all the SQL Server
2000 instances on the particular subnet.
By using SQL Server Distributed Management Objects (SQLDMO), all running
SQL Servers on a local network segment can be listed.
Since you are using VPN, would you please check if machines in VPN are in
the same subnet as that of SQL servers in LAN. Also, please confirm that
you can browse Entire network properly from VPN machines.
326362 Fix: UDL Dialog for SQL Server OLE DB Provider Generates Empty Server
http://support.microsoft.com/?id=326362
287737 INF: How to Enumerate Available SQL Servers Using SQLDMO
http://support.microsoft.com/?id=287737
If your VPN machine is in the different subnet, I am afraid it is not
possible for us to enumerate all the SQL Server instances.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.