Connect 2 databases on 2 servers ??

Hi,

If you have 2 databases with the exact same table columns, and in the first database in a table column the indentidy seed starts at 1 and finishes at 32,000 can you attatch a second database so that the indentity starts at 32,001and carries on.

What happens if you create a site and run out of disk space and need to attach another database which is located on another server?

I am really confused about this.Hope someone can give some links to some articles about this as I can't find any information about it.

How can this be done?

Thanks

robinwilliams:

If you have 2 databases with the exact same table columns, and in the first database in a table column the indentidy seed starts at 1 and finishes at 32,000 can you attatch a second database so that the indentity starts at 32,001and carries on.

Yes, you can!
Just use DTS (in SQL Server 2000) or SSIS (in SQL Server 2005) and while trasfering the data select the "Append Data" option, but make sure the data type of the identity column in the source table is able to deal with new records (have enough size) [e.g. make it INT].

robinwilliams:

What happens if you create a site and run out of disk space and need to attach another database which is located on another server?

What do you mean by another server? Is it physically another server (e.g. using another disk)? If yes, the database files (.mdf, .ndf, .ldf) will be in that server using its disks which are diffrent than the ones have no enough space.
Once you are runing out of space, you will receive error indicating that (e.g. in Enterpise Manager or Management Studio), So always have more space than you need by 2-4GB at least.

Good luck.

conlict resolution question (merge repl)

windows 2000 / sql 2000
in the conflict resolution dialog I get the 'winner' columns listed with
data and on the right 'loser' side I see this message instead of data:
"The error described above occurred when trying to insert or update this
data at the other server. If you ignore this conflict, you should resolve it
through other means. Consider logging the details of this conflict, then
sending the log entry to your system administrator"
My issue is that the subscriber machine ran out of hard drive space so all
the inserts (and some updates) were failing to get written to the
subscriber. Suprisingly sql server says thats a 'conflict'. I would rather
something like that 'fail' replication so its discovered sooner. Anyway:
1) I want to keep the update that occured at the publisher but I don't know
if thats the 'winner' or 'loser' here? its not clear. The winner side shows
data, and the loser side shows the message I pasted above. I have several of
these and I am not personally familiar with the db data so I can't tell from
looking at the data if its the 'updated' one or not. How do I know? (it sure
would be nice if the interface actually told you which machine's data you
were looking at along with 'winner' or 'loser')
Hello,
I have the same problem. I think it will be created by multiple
publications on the same table. Is this also you're replication
environment? I don't have resolved the problem at this time. If you do,
please can you send me your resolution?
djc wrote:
> windows 2000 / sql 2000
> in the conflict resolution dialog I get the 'winner' columns listed with
> data and on the right 'loser' side I see this message instead of data:
> "The error described above occurred when trying to insert or update this
> data at the other server. If you ignore this conflict, you should resolve it
> through other means. Consider logging the details of this conflict, then
> sending the log entry to your system administrator"
> My issue is that the subscriber machine ran out of hard drive space so all
> the inserts (and some updates) were failing to get written to the
> subscriber. Suprisingly sql server says thats a 'conflict'. I would rather
> something like that 'fail' replication so its discovered sooner. Anyway:
> 1) I want to keep the update that occured at the publisher but I don't know
> if thats the 'winner' or 'loser' here? its not clear. The winner side shows
> data, and the loser side shows the message I pasted above. I have several of
> these and I am not personally familiar with the db data so I can't tell from
> looking at the data if its the 'updated' one or not. How do I know? (it sure
> would be nice if the interface actually told you which machine's data you
> were looking at along with 'winner' or 'loser')

Confusing layout in SSIS with regard to "Execute SQL Task".

I hope someone can help.

I'm trying to read rows from a SQL Server Table and for each row use a few columns as parameters into a query to be run against oracle which will delete oracle rows.

I add OLDEB connections for Oracle and SQL and then I try to add a "Execute SQL Task". I've also tried a "OLE Command" but I can't get the mapping of the columns to the parameters to work.

There is lots of articles on the web that talk in general around parameterized queries but no clear examples.

I also find the difference between the Control Flow and Data Flow tabs confusing as its not intuitive where to place things. It also appears to force me to re-define things that it should already know (this is no doubt because I'm interpreting what I've done / acheived wrongly).

I have my source and destination on the "Data Flow" tab along with a "Execute SQL Task" object in the middle.

I'm setting its "connection manager" the Oracle (i.e. the destination where I want the deletes to be executed). I don't follow why this also has a "connection property, surely this it set when I drag the output of the SQL Server OLEDB Source to the input of the "Execute SQL Task".

Perhaps I'm expected too much from the wizards / dialogs and I have to create "variables" and "parameters" myself?

Any help or suggestions would be very much appreciated.

Thanks in advance

Craig

Scotland

Craig,

In general, you can think in the control flow as the one responsible for the workflow of the package; it could be also use to perform batch operations against a table. In you case, for example you could issue a delete statement using an execute sql task against the Oracle table to delete all rows at once. In order to do so, you would need to have a staging table that holds the rows that need to be delete.

The DataFlow; is deemed to move data and perform operations in a row-by-row basis. You could solve your problem by using an OLE DB command transformation to perform the delete. The drawback with this approach, it is that the delete statement would be performed for every row in the dataflow pipeline; so the performance is affected considerably.

If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction.

In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement.

I hope this helps you

|||

Thanks,

Some comments.

"If all you want to do is to delete some rows in table A when they exist in Table B; I would suggest to stick with an execute sql task that uses both tables; so it is done i one transaction."

One table exists in SQL Server, then other in Oracle, does you comment still apply?

"In order to have a 'parameterized query' you have to put the the SQL statement in a SSIS variable; set the EvaluateAsExpression=TRUE; and create an expression that gives the expected SQL statement."

MUST I do this. This does not appear to me to be in the nature of a parameterized query? Would this method still use "Prepared Statements" ?

Lastly, I'm still stuck in that my real issue is on how to map input data (from SQL) to parameters (to Oracle), I think if I followed your example, I'd still have that same problem only now I would be trying to map to a variable that was my entire query instead of just a parameter?

Thanks for the help so far and best regards

Craig

|||

Can you create and populate a staging table in the Oracle side? If so, you could load all the data in the SQL server table into a staging table in the Oracle side and then use an execute sql task to perform a 1 time update. This is the way I do this kind of things because it's more efficient performance wise.

If that is not possible; I guess you have to stick with the data flow/OLE DB command approach. But I cannot help with that as I don't have an Oracle instance to test how the parameters get mapped.

Conflicting Members

I have a report that includes a set, customer divisions as columns and products as rows. At certain intersections members are filtered by the same attribute (ie customer is filtered by product flag as is the product). Some of these have conflicting attribute values (ie product flag is true for the customer but false for the product). In these cases the customer setting is taking precedence. However I need the member setting for the product to override the customer. Is there a way of achieving this?

From your description it is not clear to me what your issue is. Axes are resolved independently, so there could be no conflicts in filtering customers and products. And if you refer to cell values, then what does filtering means ? It would be best if you will provide the exact MDX you are using and results you want to get, then this forum will be able to help you with MDX modifications.|||

In response to your question I am referring to cell values. My MDX query is basically SELECT [Customer Set] on Columns, [Product Set] on rows. I have members in my customer set and my product set which are filtered by the same attribute (Product Flag):

Product Member = ([Product - Own Keg Total] , [Product].[Product Flag].[TRUE])

Customer Member = ([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Customer].[Product Flag].[FALSE])

The values results returned indicate that the attribute value for the product flag of the customer member has been used.

Hope this makes it clearer. If not let me know. Thanks.

|||Can you please provide a definition of named sets [Customer Set] and [Product Set]. I am still not sure what you meant by Product Member and Customer Member in your response, but one thing contradicted what you said about same attribute (Product Flag) being used - in your tuple expression once you used attribute in the Product dimension and once in Customer dimension, so it cannot be the same attribute.|||

Sorry to add to the confusion. The customer member should have read:

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

Ignoring sets, if I run the query with just the product member on the row and the customer member on the column I get the same result:

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True])

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE])

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||

Thanks - it is clear now. The mechanism to control order of conflict resolution on calculated members is through SOLVE_ORDER property. I.e. in the following query Customer Test has higher solve order, therefore the Product Flag=True would win

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=1

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=2

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

And in the following query, the SOLVE_ORDER is reversed, therefore Product Flag=False will win.

WITH MEMBER [Product].[Product Hierarchy].[Product Test] AS

([Product].[Product Hierarchy].[Product Group Section].&[Own Beer]&[Own Keg], [Product].[Product Flag].[True]), SOLVE_ORDER=2

MEMBER [Customer].[Customer Hierarchy - Area].[Customer Test] AS

([Customer].[Customer Hierarchy - Area].[Market].&[United Kingdom].&[Tied Trade], [Product].[Product Flag].[FALSE]), SOLVE_ORDER=1

SELECT

[Product Test] ON COLUMNS ,

[Customer Test] ON ROWS

FROM [beer_co]

WHERE ( [Measures].[Barrelage] )

|||Is there a way of configuring the query so that where there is a conflict neither member wins and nothing is returned?