Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.
Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert
|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert
sql

Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert

Dynamic object location

Hi, i would like to know if there's a way to set the objects location/size
based on user input parameters. I try to use expressions in location and size
properties but rs says that is a invalid value for the field.
Thanks
Jorge"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge|||I am interested in doing this as well. In particular I want to be able to
compute the height of a rectangle control based on data values.
Bill
"Jorge Gonçalves" <JorgeGonalves@.discussions.microsoft.com> wrote in message
news:90CF28CC-066F-46BB-9664-75929DEE8EC3@.microsoft.com...
> Hi, i would like to know if there's a way to set the objects location/size
> based on user input parameters. I try to use expressions in location and
size
> properties but rs says that is a invalid value for the field.
> Thanks
> Jorge

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic feed of table name to Transfer SQL Server Object Task

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @.table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @.table_name

I would be grateful if you can give me any hint.
Thanks
S

The logic looks right to me...are you seeing any error?

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you

|||

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit

|||Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.

|||Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks
|||

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit

|||thanks|||I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?|||

Kolf wrote:

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"

sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas?

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit

|||it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks|||

Kolf wrote:

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution?
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit

|||

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

|||

Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit

|||Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks|||Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks

Dynamic embedded image...

Hey all,

I am trying to add an image object to a report that has its value property set dynamically and am not being very successful. I have two images embedded in the report called greyFlag and orangeFlag. When I try to add the condition:
...
=IIf(Fields!DateFilled.Value >= DateTime.Now.AddMonths(-12), "orangeFlag", "greyFlag")
...
I get the error:
...
The value of the value property for the image ‘image2’ is "greyFlag", which is not a valid value.

...

What am I doing wrong here?

Regards,

Stephen.

You need to put the code in the visible expression field.

in the Hidden for Greyflag field add the expression

=IIf(Fields!DateFilled.Value >= DateTime.Now.AddMonths(-12), true, false)

ANd do the opposite for the orangeFlag

=IIf(Fields!DateFilled.Value >= DateTime.Now.AddMonths(-12), false,true)

|||

OK. I was hoping to use just one image and replace its source, but I've used your technique by overlaying two images.

Thanks for the help!

Regards,

Stephen.

Dynamic Dynamic Filters

Hello,
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:

> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:

> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>

Dynamic database with MS ACCESS

Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>