How to Change Subscriber Status in Merge Publication

Tony,
as a bit of background, what is your requirement? What is
ti you need to achieve by trying to change the status, as
there might be other more straightforward ways of
achieving the same ends.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
I have a .Net app that handles the creation and deletion of
subscriptions for all my clients. I need to have an administrator using
the application disable a specific user at anytime without having to go
into EM to perform the task.
hope this helps
Thanks!
Tony D
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Tony,
I'm not sure if this is possible using Stored Procs but using DMO there is a
status property of the mergeSubscription object that is read/write. As
you're handling things programatically anyway this should be ok. There's a
reference n BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\sq
ldmo.chm::/dmoref_p_s_4w8i.htm) that might help.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

how to change data types in Excel source file?

I'm getting a bit lost in SSIS. I've got an Excel source file that I'm trying to load into a table. I keep getting validation errors that warn about not being able to convert between unicode and non-unicode string data types.

I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.

On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.

I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?

That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.

Any help would be appreciated!

The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.

Try using the Import/Export wizard to generate a sample package for this.

|||

Hi Bob,

What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.

If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.

Thanks & Regards,

Prakash Srinivasan.

|||

I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.

From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.

So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?

Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?

BOL does not seem to offer any meaningful information here.

|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')

How To Change All Records in a Single Field?

Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer=92s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone=92s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!Create a view and show them the results of the view.
CREATE VIEW dbo.foo
AS
SELECT col1, col2, AccountNumber = 'foo', col3, ...
FROM dbo.Original_Table;
<alvinstraight38@.hotmail.com> wrote in message
news:d2e508c2-4c8e-4627-90dc-5b7e38dabb91@.e39g2000hsf.googlegroups.com...
Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer?s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone?s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!|||UPDATE ThatTable
SET SomeColumn = 'NewValue'
Since there is no WHERE clause to limit the effect, every row in
ThatTable will be updated.
The problem with changing a customer's account number that way is that
it is likely to violate a UNIQUE constraint, as well as appearing in
many tables. It is likely to be used for JOINs.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 06:18:42 -0700 (PDT),
"alvinstraight38@.hotmail.com" <alvinstraight38@.hotmail.com> wrote:
>Hey guys,
>I am in a bit of a pickle. I have to do a software training today,
>and my training database is hosed. I have a production database, but
>I do not want the users to actually see a customer?s live data on the
>screen. I want to make a global alternation to all records on a
>single field such as change everyone?s account number to a specific
>value.
>Is there a way to do this in SQL 2005?
>Thanks!

How to CASE a SmallInt to a Varchar value

Why does this syntax work for bit but not for smallint ?
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:

> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>