Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."

>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic Oledb Destination

Hello,

I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".

My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.

I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.

I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.

Highly appreciate any help in this regard.

Regards

Sameer

Sameer,

The OLE DB Destination does not have a connection string property however the OLE DB Connection Manager that it uses DOES. You can change this connection string so that you are pointing at (e.g.) a different server or a different .mdb file.

What you cannot do however is change the name of the table in the OLE DB Destination that you are inserting into. Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time.

If you are inserting into 100 identical MS Access databases then you can do this by modifying the connection string property of the OLE DB Connection Manager and looping using the For Each Loop container. If you want to insert to 100 different tables then you need 100 different data-flows.

-Jamie

|||

Jamie,

Thanks for the reply. Yes you are right it is the OLEDB Connection manger.

My requirement is that the these are 100 identical MS access tables which needs to be loaded with production data after every 1 hour. Would like to know the details of looping the For Each loop container using my Configuration table, which holds information about MDB file locations. How do I configure the For each loop container to point to the Configuration table. I have now started feeling that connection manager goes well only with the flat file and does not go well with OLEDB.

Can you provide me a small example which loops the files. Also these access files are spread across the network, and many time the network connectiion goes down, hence I have made an extra column in my table which updates that the data has reached/Unreached to final destination. How do i capture the same in SSIS. In old traditional way of DTS I remember I used to have privledge of doing the

Set oPkg = DTSGlobalVariables.Parent through which I could find out the execution result like this, which is oPkg.steps(i).ExecutionResult. How do I achieve the same in SSIS.

Again if you could provide me an hint or working example that would be great. I do have working example for flat file connection manager , but I am unable to replicate the same using OLEDB destination.

Thanks in advance for the suggestions

Regards

|||

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

|||

bowmandba wrote:

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

You need to use expressions.

SSIS>>Expressions

(http://blogs.conchango.com/jamiethomson/archive/tags/SSIS/Expressions/default.aspx)

Any questions, reply here.

-Jamie

Dynamic Oledb Destination

Hello,

I am a beginner for the SSIS and would like to know how to modify the OLDEB Destination connectionString property at run time like using "for each loop container".

My requirement is that I have a single source which would be Sql Server 2005 and my destination is in MS-Access database residing in 100 places. I do not want to manually design in the data flow to these 100 destinations.

I have all the destinations stored in a table and would like to pick these destinations from the table and loop through the same at run time by modifying destination connection string.

I have planned using dts but the for each loop container does not work through as it works with flat file connection manager , but does not go well with OLDEB connection.

Highly appreciate any help in this regard.

Regards

Sameer

Sameer,

The OLE DB Destination does not have a connection string property however the OLE DB Connection Manager that it uses DOES. You can change this connection string so that you are pointing at (e.g.) a different server or a different .mdb file.

What you cannot do however is change the name of the table in the OLE DB Destination that you are inserting into. Well actually you can but the metadata (i.e. column names, types) of the table into which you are inserting must be the same as that selected at design-time.

If you are inserting into 100 identical MS Access databases then you can do this by modifying the connection string property of the OLE DB Connection Manager and looping using the For Each Loop container. If you want to insert to 100 different tables then you need 100 different data-flows.

-Jamie

|||

Jamie,

Thanks for the reply. Yes you are right it is the OLEDB Connection manger.

My requirement is that the these are 100 identical MS access tables which needs to be loaded with production data after every 1 hour. Would like to know the details of looping the For Each loop container using my Configuration table, which holds information about MDB file locations. How do I configure the For each loop container to point to the Configuration table. I have now started feeling that connection manager goes well only with the flat file and does not go well with OLEDB.

Can you provide me a small example which loops the files. Also these access files are spread across the network, and many time the network connectiion goes down, hence I have made an extra column in my table which updates that the data has reached/Unreached to final destination. How do i capture the same in SSIS. In old traditional way of DTS I remember I used to have privledge of doing the

Set oPkg = DTSGlobalVariables.Parent through which I could find out the execution result like this, which is oPkg.steps(i).ExecutionResult. How do I achieve the same in SSIS.

Again if you could provide me an hint or working example that would be great. I do have working example for flat file connection manager , but I am unable to replicate the same using OLEDB destination.

Thanks in advance for the suggestions

Regards

|||

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

|||

bowmandba wrote:

Greetings Sameer,

I am working on a similar issue. have you found any resolutions for dymanic configuration of an oledb connection?

my task is smiliar. I plan to dymanically change (table drive) oledb connection "servernames" and query each of these servername, which will in turn write to a single datasource. I am stuck on the dynamic configuration of these oledb sources.

Any feedback you can provide would be greatly appreciated.

Thanks

You need to use expressions.

SSIS>>Expressions

(http://blogs.conchango.com/jamiethomson/archive/tags/SSIS/Expressions/default.aspx)

Any questions, reply here.

-Jamie