How to Add Subscription or Subscribers manually through scripts instead of programming

bruce_yang72


Hello everyone,

I am new for SQL SERVER 2005 NS. I have gone through some samples about NS. Most of them add subscriptions and subscribers through vbs or .net application. So I got some questions to ask for your help:

1. I just wonder if there are some ways for me to add subscriptions and subscribers manually through T-SQL scripts instead of programming. Because sometimes, we don't need to ask our users to subscribe our message.


2. Is that possible for NS to send notification by SMTP without adding subscription For example, I have already populated all emails from database, what I need is just to send simple notes to these emails, and at this time, I don't need users to subscribe them. I just need to get NS works as database mail at this point - only send email notification without subscription.
I am not sure if this is possible. ( I know with databse mail, this can be done easily, how about with SQL NS, any idea )


Would you please give me some advices I appreciate your help in advance.

Best Regards,

Joe Webb


Hi -


1. Sure. The following script demonstrates how to create a subscriber, subscriberdevice, and subscription using TSQL. This is a snippet from a recent SSNS presentation I did. You can download the full presentation and scripts from my blog. Here's a directly link:

http://weblogs.sqlteam.com/joew/archive/2007/10/15/60373.aspx


-------------------------------
--CREATE A SUBSCRIBER & DEVICE
-------------------------------
USE ABCPressNSMain
GO

--CREATE A SUBSCRIBER
INSERT NSSubscriberView (SubscriberId, Enabled)
VALUES ('joew@webbtechsolutions.com', 1)

--CREATE A SUBSCRIBER DEVICE
INSERT NSSubscriberDeviceView (SubscriberId, Enabled, DeviceName, DeviceTypeName, DeviceAddress, DeliveryChannelName)
VALUES ('joew@webbtechsolutions.com', 1, 'EmailDevice', 'Email', 'joew@webbtechsolutions.com', 'EmailChannel')



-------------------------------
--CREATE A SUBSCRIPTION
-------------------------------
USE ABCPressPressReleases
GO

--CREATE A SUBSCRIPTION
INSERT NSPrSubscriptionView(SubscriberId, Enabled, SubscriberDeviceName, SubscriberLocale, PrType)
VALUES ('joew@webbtechsolutions.com', 1, 'EmailDevice', 'en-us', 1)


--VIEW THE SUBSCRIPTION INFO
SELECT
*
FROM
NSPrSubscriptionView



2. No. SSNS works with in a subscription methodology only.

HTH...

Joe

bruce_yang72


Hi Joe,

Thanks to your kind help! It does help me a lot.

I appreciate your help.